Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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)

    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).

    Comment


    • #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)

      Comment

      Working...
      X