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

User · Accepted 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] SELECT * WHERE NOT EXISTS

Examples related to mysql