| 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
|
|
|
|
| Re: select difference between 2 tables (rows from table b that are not in table a) [message #1674 is a reply to message #1662 ] |
Wed, 22 August 2007 21:36  |
RichardBronosky
Messages: 20
Registered: June 2007 |
Junior Member |
|
|
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)
[Updated on: Wed, 22 August 2007 21:36]
.+# Richard Bronosky #+.
MySQL DBA, MythTV User
|
|
|
Members
Search
Help
Register
Login
Home
