CODEWITHSUNDEEP
mysqlnot-exists
frusDev
frusDev

Reputation: 1978

SELECT * WHERE NOT EXISTS

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?

Upvotes: 135

Views: 380105

Answers (5)

Quassnoi
Quassnoi

Reputation: 425371

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.

Upvotes: 205

Andre Miller
Andre Miller

Reputation: 15493

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.

Upvotes: 4

Cade Roux
Cade Roux

Reputation: 89661

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.

Upvotes: 9

Robin Day
Robin Day

Reputation: 102478

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

Upvotes: 95

Mike Tunnicliffe
Mike Tunnicliffe

Reputation: 10772

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

Upvotes: 14

Related Questions