select difference between 2 tables (rows from table b that are not in table a)

MySQL & MariaDB Other MySQL® Questions
1

select difference between 2 tables (rows from table b that are not in table a)

I thought I once saw a topic on this task, right here on this forum. But, today when I need it… It’s no where to be found.

Anyway, here is the situation:

use test;drop table if exists a;drop table if exists b;create table a (x int, y int);insert into a values (1,2),(3,4),(5,6),(7,8),(9,0);create table b as select * from a where x<7;select * from a;select * from b;

…yields…

±-----±-----+| x | y |±-----±-----+| 1 | 2 | | 3 | 4 | | 5 | 6 | ±-----±-----+±-----±-----+| x | y |±-----±-----+| 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8 | | 9 | 0 | ±-----±-----+

I want to write a select statement that will return the difference between table a and table b. Stated another way… I want to know what is unique to table a when compared to table b.

The result should be:

±-----±-----+| x | y |±-----±-----+| 7 | 8 | | 9 | 0 | ±-----±-----+

… but how do you select it?

2

SELECT …FROM aLEFT JOIN b ON a.id = b.idWHERE b.id IS NULL;

The LEFT JOIN and the IS NULL is the trick that you are after.
You can use a sub select and IN() also, but I’m old school (pre subselect in mysql). :wink:

3

Thanks for the pointer, but to keep with the example, I’ll demonstrate it this way.

(root@localhost) [test]> SELECT * FROM a;±-----±-----+| x | y |±-----±-----+| 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8 | | 9 | 0 | ±-----±-----+5 rows in set (0.00 sec)(root@localhost) [test]> SELECT * FROM b;±-----±-----+| x | y |±-----±-----+| 1 | 2 | | 3 | 4 | | 5 | 6 | ±-----±-----+3 rows in set (0.00 sec)(root@localhost) [test]> SELECT a.* FROM a LEFT JOIN b USING (x) where b.x IS NULL;±-----±-----+| x | y |±-----±-----+| 7 | 8 | | 9 | 0 | ±-----±-----+2 rows in set (0.00 sec)(root@localhost) [test]> SELECT * FROM a where x NOT IN (select x from b);±-----±-----+| x | y |±-----±-----+| 7 | 8 | | 9 | 0 | ±-----±-----+2 rows in set (0.00 sec)