Intersection and difference

from the Artful Common Queries page


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

Return to the Artful Common Queries page