MySQL Performance: MySQL => select difference between 2 tables (rows from table b that are not in table a)

select difference between 2 tables (rows from table b that are not in table a) [message #1658]

Tue, 21 August 2007 16:35

RichardBronosky
Messages: 20
Registered: June 2007

Junior Member

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?

.+# Richard Bronosky #+.
MySQL DBA, MythTV User

Report message to a moderator