UNION, INTERSECT, and EXCEPT

Set operators combine whole results: UNION returns rows from either query, INTERSECT rows in both, EXCEPT rows of the first absent from the second. INTERSECT and EXCEPT arrived in MySQL 8.0.31 524 . Operands may be SELECT, TABLE or VALUES statements with equal column counts.

Products sold but never reviewed, and one customer's activity feedSQL

SELECT product_id FROM order_items EXCEPT SELECT product_id FROM reviews;
SELECT 'order' AS kind, id, ordered_at AS happened FROM orders WHERE customer_id = 1
UNION ALL
SELECT 'review', id, created_at FROM reviews WHERE customer_id = 1
ORDER BY happened;

Output

+------------+
| product_id |
+------------+
|          5 |
|          8 |
+------------+
2 rows in set (0.000 sec)
+--------+----+---------------------+
| kind   | id | happened            |
+--------+----+---------------------+
| order  |  1 | 2026-03-02 10:15:00 |
| review |  1 | 2026-03-20 18:00:00 |
| order  |  4 | 2026-05-05 12:00:00 |
| review |  5 | 2026-05-20 07:00:00 |
+--------+----+---------------------+
4 rows in set (0.001 sec)

All three remove duplicates unless you add ALL; the feed's halves cannot overlap, so UNION ALL skips that work. INTERSECT ALL keeps each row as often as its smaller count. Column names come from the first operand, and a trailing ORDER BY or LIMIT applies to the whole result (parenthesize an operand to limit it alone). INTERSECT binds tighter, so a EXCEPT b INTERSECT c means a EXCEPT (b INTERSECT c). NULLs compare as equal, as in DISTINCT. Oracle's MINUS is a syntax error.

MENU

UNION, INTERSECT, and EXCEPT