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?
This question is related to
mysql
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.
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
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.
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
Source: Stackoverflow.com