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 'a' as tbl, i, j from a left join b using(i,j) where b.i is null union select 'b', i, j from b left join a using(i,j) where a.i is null; +-----+------+------+ | tbl | i | j | +-----+------+------+ | a | 2 | 2 | | b | 3 | 3 | +-----+------+------+
Last updated 30 Jun 2024
