Intersection and Difference in MySQL

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   |
+------+------+