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.

what are these values? ids? providing the table structure would actually make this easier on us — Jonathan Fingland, Jul 03 '09 at 14:28
Sorry, yes, they are IDs. I had originally provided more info but then I thought I'd pare down to only the relevant bits. Apparently I pared too far. — Eileen, Jul 03 '09 at 14:34

score 2 · Answer 1 · answered Jul 03 '09 at 14:26

2

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)

answered Jul 03 '09 at 14:26

Jonathan Fingland

I'd be very curious to see how this performs against the answer cletus provided. My initial thought was to answer with what he wrote but would't this be faster? Mmm. – Paolo Bergantino Jul 03 '09 at 14:28
If table B can contain NULL values, NOT IN is a dangerous tool, see http://stackoverflow.com/questions/129077/sql-not-in-constraint-and-null-values Consider using NOT EXISTS. – Andomar Jul 03 '09 at 14:40

score 2 · Accepted Answer · answered Jul 03 '09 at 14:26

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.

answered Jul 03 '09 at 14:26

cletus

Ah, that's perfect! Thanks! Performance isn't an issue here as this this is a one-time query. – Eileen Jul 03 '09 at 14:33

score 0 · Answer 3 · answered Jul 03 '09 at 14:28

0

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

answered Jul 03 '09 at 14:28

instanceof me

That's up to the query optimizer. For example, Sql Server will execute both queries in the same way. – Andomar Jul 03 '09 at 14:42
Yes, but the question being for mysql, it is said in the doc that this query is (still in 6.0 apparently) messed up by the optimizer. – instanceof me Jul 04 '09 at 19:11

3 Answers3