CHOOSE * WHERE DOES NOT EXIST

Question

I think I am going the right way with this ... Please bear with me, as my SQL is not the biggest

I am trying to query a database to select everything from one table where certain cells do not exist in another. It doesn’t make much sense, but I hope this piece of code will be

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. If the name is not in the eotm_dyn table, that is, there is no record for them, I would like to indicate exactly who they are, or, in other words, see what exactly is missing.

The above query returns nothing, but I know that there are 20-digit names, so I obviously did not get it right.

Can anyone help?

Answer 1

You have not joined the table in your query.

Your original query will always return nothing if eotm_dyn are no records in eotm_dyn at all, in which case it will return everything.

Assuming these tables should be concatenated into 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 the LEFT JOIN keyword and filter out NULL , but this is likely to be less efficient than using NOT EXISTS .

Answer 2

 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 

Answer 3

You can make a LEFT JOIN and claim that 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 

Answer 4

 SELECT * from employees WHERE NOT EXISTS (SELECT name FROM eotm_dyn) 

Never returns any records if eotm_dyn not empty. For SELECT name FROM eotm_dyn you need some criteria, for example

 SELECT * from employees WHERE NOT EXISTS ( SELECT name FROM eotm_dyn WHERE eotm_dyn.employeeid = employees.employeeid ) 

assuming the two tables are related by a foreign key relationship. At this point you can use many other options, including LEFT JOIN. However, the optimizer usually processes them in most cases.

Answer 5

You can also see this related question . This user reported that using a connection provides better performance than using an auxiliary query.