CODEWITHSUNDEEP
mysql
diesel
diesel

Reputation: 3415

How do I properly use NOT EXISTS for MySQL?

I'm trying to display a list of items that do not already exist in another table. For instance:

usertable:

uid
name
email

othertable:
uid

I'm trying to filter out users that already exist in the other table, so when I display the usertable, it will only show users that are not in the othertable.

Upvotes: 0

Views: 895

Answers (2)

redShadow
redShadow

Reputation: 6777

NOT EXIST in mysql is completely another thing from what you want to do. UPDATE: This is not true! (thanks for pointing out)

I understand you want to select all records that are in usertable but not in othertable, right?

SELECT * FROM usertable WHERE uid NOT in (SELECT uid FROM othertable)

UPDATE In case you want to check all the fields in the row, not only the ID, you can use something like:

SELECT * FROM usertable
WHERE field1, field2, fieldN NOT IN
(SELECT field1, field2, fieldN FROM othertable)

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

This would be simpler with a LEFT JOIN:

SELECT u.*
FROM   usertable u
LEFT   JOIN othertable o USING (uid)
WHERE  o.uid IS NULL

If you want to have NOT EXISTS:

SELECT *
FROM   usertable u
WHERE  NOT EXISTS (
    SELECT *
    FROM othertable o
    WHERE o.uid = u.uid
    )

Upvotes: 2

Related Questions