SQL: select all unique values in table A which are not in table B

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 Ids in table A which do not exist in table B. how can I do 开发者_如何学编程this?

---

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

---

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

The "good" solution is usually MINUS or EXCEPT, but MySQL doesn't support it.

This question was asked a few time ago and someone posted an article comparing NOT IN, NOT EXISTS and LEFT OUTER JOIN ... IS NULL. It would be interesting if someone could find it again!

---

The most efficient answer is to use a left join, as using "NOT IN" can sometimes prevent a query from using an index, if present.

The answer in this case would be something like

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 the NOT IN solutions

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

---

I'd use a NOT EXISTS Like this:

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

---

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

---

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

---

The subquery will get all the IDs in B. The group by...having will get all the unique IDs 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;