SELECT * WHERE NOT EXISTS

Question

I think I'm going down the right path with this one... Please bear with me as my SQL isn't the greatest

I'm trying to query a database to select everything from one table where certain cells don't exist in another. That much doesn't make a lot of sense but I'm hoping this piece of code will

SELECT * from employees WHERE NOT EXISTS (SELECT name FROM eotm_dyn)

So basically I have one table with a list of employees and their details. Then another table with some other details, including their name. Where there name is not in the eotm_dyn table, meaning there is no entry for them, I would like to see exactly who they are, or in other words, see what exactly is missing.

The above query returns nothing, but I know there are 20ish names missing so I've obviously not gotten it right.

Can anyone help?

score 194 · Accepted Answer · edited Jun 05 '14 at 09:54

194

You didn't join the table in your query.

Your original query will always return nothing unless there are no records at all in eotm_dyn, in which case it will return everything.

Assuming these tables should be joined on employeeID, use the following:

SELECT  *
FROM    employees e
WHERE   NOT EXISTS
        (
        SELECT  null 
        FROM    eotm_dyn d
        WHERE   d.employeeID = e.id
        )

You can join these tables with a LEFT JOIN keyword and filter out the NULL's, but this will likely be less efficient than using NOT EXISTS.

edited Jun 05 '14 at 09:54

soqls

26
5

answered May 27 '09 at 13:19

Quassnoi

413,100
91
616
614

39

I need "WHERE NOT EXISTS" twice a year, and I always forget how to exactly use it. Thanks - this example will be bookmarked now. – Mateng Sep 18 '12 at 09:00
2

Could someone please give a reference for "LEFT JOIN + NULL filter is less efficient than NOT EXISTS"? It can be obvious, but I never saw that in the docs. Thanks. – toni07 Feb 16 '15 at 16:39
3

@toni07 Actually, that is a legend. LEFT JOIN wins. https://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ .. Quassnoi's blog is always a helpful resource. – Kaii Apr 26 '16 at 12:51
how would I use this in a HAVING clause? aka `group by X having exist [row with employeeID = e.id]` – phil294 Jul 07 '16 at 15:45
@blauhirn: just like that – Quassnoi Jul 07 '16 at 16:21

score 91 · Answer 2 · answered May 27 '09 at 13:20

91

SELECT * FROM employees WHERE name NOT IN (SELECT name FROM eotm_dyn)

OR

SELECT * FROM employees WHERE NOT EXISTS (SELECT * FROM eotm_dyn WHERE eotm_dyn.name = employees.name)

OR

SELECT * FROM employees LEFT OUTER JOIN eotm_dyn ON eotm_dyn.name = employees.name WHERE eotm_dyn IS NULL

answered May 27 '09 at 13:20

Robin Day

100,552
23
116
167

1

NB! `NOT IN` does not work as expected if `name`has `null`values. Watch from 36min 20sec in the video [SESSION: 10 Query Tuning Techniques Every SQL Programmer Should Know (Kevin Kline, Aaron Bertrand)](https://www.youtube.com/watch?v=XUCxQkFoqpw). – hlovdal Sep 02 '16 at 11:01
you can flter with "Where name IS Not NULL" in sub query – M. Hamza Rajput Jan 03 '21 at 17:10
In addition, ```NOT IN``` is not as fast as ```NOT EXISTS``` – Sophia May 01 '22 at 18:59

score 13 · Answer 3 · answered May 27 '09 at 13:19

13

You can do a LEFT JOIN and assert the joined column is NULL.

Example:

SELECT * FROM employees a LEFT JOIN eotm_dyn b on (a.joinfield=b.joinfield) WHERE b.name IS NULL

answered May 27 '09 at 13:19

Mike Tunnicliffe

10,674
3
31
46

score 9 · Answer 4 · answered May 27 '09 at 13:24

SELECT * from employees
WHERE NOT EXISTS (SELECT name FROM eotm_dyn)

Never returns any records unless eotm_dyn is empty. You need to some kind of criteria on SELECT name FROM eotm_dyn like

SELECT * from employees
WHERE NOT EXISTS (
    SELECT name FROM eotm_dyn WHERE eotm_dyn.employeeid = employees.employeeid
)

assuming that the two tables are linked by a foreign key relationship. At this point you could use a variety of other options including a LEFT JOIN. The optimizer will typically handle them the same in most cases, however.

score 4 · Answer 5 · edited May 23 '17 at 12:26

4

You can also have a look at this related question. That user reported that using a join provided better performance than using a sub query.

edited May 23 '17 at 12:26

Community

1
1

answered May 27 '09 at 13:26

Andre Miller

15,255
6
55
53

SELECT * WHERE NOT EXISTS

5 Answers5

Linked

Related