How do I properly use NOT EXISTS for MySQL?

Question

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.

possible duplicate of [MySQL "NOT IN" query](http://stackoverflow.com/questions/1519272/mysql-not-in-query) — CanSpice, Dec 13 '11 at 23:18
This is an exact duplicate of http://stackoverflow.com/questions/915643/select-where-not-exists — The Nail, Dec 13 '11 at 23:23

redShadow · Accepted Answer · 2011-12-13T23:35:18.743

2

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)

edited Dec 13 '11 at 23:35

answered Dec 13 '11 at 23:18

redShadow

6,687
2
31
34

That is correct! Thanks! Am I also able to add a WHERE clause in the second select? – diesel Dec 13 '11 at 23:20
1

`NOT EXISTS` is a perfectly valid approach for the case. – Erwin Brandstetter Dec 13 '11 at 23:24
@diesel Sure, the second query is a normal query returning a set of results that will be used for the `... IN ...` filter – redShadow Dec 13 '11 at 23:27
@ErwinBrandstetter damn, I love learning new things.. I always thought `NOT EXISTS` was only for `CREATE TABLE` and such.. – redShadow Dec 13 '11 at 23:28
That's `IF NOT EXISTS`. See here what you can actually do with `WHERE (NOT) EXISTS`: http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html – The Nail Dec 13 '11 at 23:29
yep.. I just googled that too :) anyways, it looks more readable (to me) by just using the sub-query, doesn't it? Plus, how would you write the "an equal record with different id exists" thing, using `NOT EXISTS` ..? – redShadow Dec 13 '11 at 23:34

score 2 · Answer 2 · answered Dec 13 '11 at 23:18

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
    )

How do I properly use NOT EXISTS for MySQL?

2 Answers2