CODEWITHSUNDEEP
mysql
Adriyana Putra Pratama
Adriyana Putra Pratama

Reputation: 166

How to inner join with not in or not exist in mysql query?

I have some data like this for user

id | username | email
1  | test     | [email protected]
2  | om       | [email protected]
3  | aa       | [email protected]

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?

Upvotes: 3

Views: 7647

Answers (2)

O. Jones
O. Jones

Reputation: 108706

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.

Upvotes: 5

Leonardo
Leonardo

Reputation: 801

She wants the names (user.username) that are not present in biodata table. Left Join preserves ALL records from table users and MATCHING records fom table biodata (good explanation in W3Schools Left Join). The where clause searches usernames that are not present in biodata:

SELECT u.id, u.username, u.email 
FROM user u 
LEFT JOIN biodata bd 
ON u.username = bd.username 
WHERE u.username 
NOT IN (select username from biodata);

The correct output is:

id | username | email
3  |    aa    | [email protected]

I belive that you are trying to do an 'EXCEPT'. But some SGBDS like MySQL don't have this option.

Except

Upvotes: 4

Related Questions