mysql查询不存在,如何在MySQL查询中不存在或不存在内部联接？

I have some data like this

for user

id | username | email

1 | test | test@test.com

2 | om | test2@test2.com

3 | aa | test3@test3.com

And I have data user biodata like this

id | username | bio

1 | test | test

2 | om | test2

So, I want use not in with inner join for showing data with where user.username not in biodata.username and I try like this but it's error

select user.*, biodata.* from user inner join biodata on user.username = biodata.username where user.username not in biodata.username;

So, how to use that?

解决方案

This calls for the common LEFT JOIN ... IS NULL pattern.

SELECT u.id, u.username, u.email

FROM user u

LEFT JOIN biodata b ON u.username = b.username

WHERE b.id IS NULL

The LEFT JOIN operation preserves all rows in user, whether or not they have matches in biodata. (By contrast, an ordinary JOIN would suppress rows from user that didn't have matches.) The b.id IS NULL operation filters out the rows that do have matches.