CODEWITHSUNDEEP
mysql
Bob Herrmann
Bob Herrmann

Reputation: 9908

How to find items not in a table

My database has,

mysql> select * from peeps;
+-------+
| name  |
+-------+
| tom   |
| dick  |
| harry |
+-------+

I want to write a sql statement to return a result set of users not in that table.

sql ????  where name in ( 'dave', 'tom')

I want a result set which returns 'dave'. The answer can't return 'harry'. I'm not keen on creating a new temporary table, can this be dont in a single statement.

Thanks!

Upvotes: 1

Views: 38

Answers (1)

Kermit
Kermit

Reputation: 34055

Would something like this work for you? It returns dave as requested.

SELECT a.name
FROM
    (SELECT 'dave' AS name
    UNION ALL
    SELECT 'tom' AS name) a
LEFT JOIN peeps b ON b.name = a.name
WHERE b.name IS NULL

Result

| NAME |
--------
| dave |

SQL Fiddle

Upvotes: 2

Related Questions