Ciaran Ciaran - 1 month ago 10
MySQL Question

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?

Answer

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.