mysql - 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?

Answer 1

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.