Intersection and difference

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 |

+------+------+