This post has already been read 1842 times!

MySQL implements UNION, but does not directly implement INTERSECTION or DIFFERENCE.

INTERSECTION is just an INNER JOIN on all columns:

drop table if exists a,b;

create table a(i int,j int);
create table b like a;

insert into a values(1,1),(2,2);
insert into b values(1,1),(3,3);

select * from a join b using(i,j);
+------+------+
| i    |    j |
+------+------+
| 1    |    1  |
+------+------+

Get the DIFFERENCE between tables a and b by UNIONing exclusion joins from a to b, and from b to a:

select * from a left join b using(i,j) where b.i is null
union
select * from b left join a using(i,j) where a.i is null;

+------+------+
|  i   |   j  |
+------+------+
|  2   | 2   |
|  3   | 3   |
+------+------+

Comments are closed.

Post Navigation