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?
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?
Comment