MySQL - How to get a list of values in Table A that don't exist in Table B?

Question

**Table A**
1
2
3
4
5
6


**Table B**
2
3
5

How can I select for entry IDs that only exist in Table B? In this example, I'm looking for a query that returns 1, 4, and 6.

Answer 1

Try

select value from B where value not in (select value from A)

to get values in B that are not in A.

To get the opposite (values in A not in B) use

select value from A where value not in (select value from B)

Answer 2

Assuming the column is named 'id', either:

SELECT *
FROM tableA a
WHERE NOT EXISTS (SELECT 1 FROM tableB WHERE id = a.id)

or

SELECT *
FROM TableA
WHERE id NOT IN (SELECT id FROM tableB)

You will probably need to test to see which performs best. MySQL can be a bit unpredictable.

Answer 3

This avoids IN + subquery:

SELECT A.value FROM A
LEFT OUTER JOIN B ON (A.value = B.value)
WHERE B.value IS NULL

Because IN (subquery) isn't optimized as it is executed for each found row in table A