SQL: select all unique values ​​in table A that are not listed in table B

Question

I have table A

Id | Name | Department ----------------------------- 0 | Alice | 1 0 | Alice | 2 1 | Bob | 1 

and table B

 Id | Name ------------- 0 | Alice 

I want to select all unique identifiers in table A that are not in table B. How can I do this?

Answer 1

 select distinct id from TableA a where not exists ( select id from TableB where id = a.id ) 

Answer 2

Just to provide a different solution than NOT IN:

 SELECT DISTINCT A.Id FROM A LEFT OUTER JOIN B ON A.Id = B.Id WHERE B.Id IS NULL 

A "good" solution is usually MINUS or EXCEPT, but MySQL does not support it.

This question was asked several times ago, and someone published an article comparing NOT IN, NOT EXISTS and LEFT OUTER JOIN ... NULL. It would be interesting if anyone could find him again!

Answer 3

The most effective answer is to use a left join, since using "NOT IN" can sometimes prevent the query from using the index, if any.

The answer in this case will be similar to

 SELECT DISTINCT * FROM TableA a LEFT JOIN TableB b ON a.Id = b.Id WHERE b.Id IS NULL 

Alternatively, this is more readable than a left join, and more efficient than NOT IN solutions.

 SELECT * FROM TableA a where NOT EXISTS (SELECT * FROM TableB where Id = a.Id) 

Answer 4

I would use NOT EXIST:

 SELECT A.Id FROM TableA A WHERE NOT EXISTS (SELECT B.Id FROM TableB B WHERE A.Id = B.Id) GROUP BY A.Id 

Answer 5

 SELECT DISTINCT Id FROM A WHERE Id NOT IN (SELECT ID FROM B); 

Answer 6

 SELECT DISTINCT Id FROM A WHERE Id NOT IN(SELECT DISTINCT Id FROM B); 

Answer 7

The subquery will receive all IDs in B. group by...having will receive all unique identifiers in A that are also not in B.

 select * from A where id not in ( select distinct id from B ) group by ID having count(*) > 1;