Set Theory in Practice

Both data sets must have the same number of columns.
The data types of each column across the two data sets must be the same (or the server must be able to convert one to the other).

select 1 num, 'abc' str
union
select 9 num, 'xyz' str;

select 1 num, 'abc' str
union
select 9, 'xyz';

Set Operators

Union

union: sorting the combined data and removing duplicates.

union all: combining all into one without asking.

select 'cust' typ, c.first_name, c.last_name
from customer as c
union all
select 'actr', a.first_name, a.last_name
from actor as a;

select c.first_name, c.last_name
from customer as c
where c.first_name like 'J%' and c.last_name like 'D%'
union
select a.first_name, a.last_name
from actor as a
where a.first_name like 'J%' and a.last_name like 'D%';

Intersect

SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
INTERSECT
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';

Non-excusable-on-mysql code due to the lack of intersect implementation in version 8.0.

<aside> 💡 The only database server that currently implements the intersect all operator is IBM’s DB2 Universal Server.

</aside>

Except

The except operator returns the first result set minus any overlap with the second result set.

SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
EXCEPT
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';