INTERSECT
Return only the rows present in every query block — available from MySQL 8.0.31, with the join that stood in for it before.
SELECT ... INTERSECT [ALL | DISTINCT] SELECT ...INTERSECT returns the rows that appear in all of its query blocks. Like UNION it compares whole rows column by column, matching by position rather than by name, and like UNION it de-duplicates by default.
Version
MySQL added INTERSECT and EXCEPT in 8.0.31, released October 2022. MySQL 8.4 LTS has both. On anything older the keyword is a syntax error, which is why a great deal of existing code does this job with a join or a subquery instead — worth recognising, since those forms are still the ones you will meet in older codebases.
DISTINCT and ALL
INTERSECT and INTERSECT DISTINCT are the same statement: duplicates are removed from either side. INTERSECT ALL retains duplicate rows instead of collapsing them.
Precedence
INTERSECT binds tighter than UNION and EXCEPT. TABLE r EXCEPT TABLE s INTERSECT TABLE t is evaluated as TABLE r EXCEPT (TABLE s INTERSECT TABLE t). Mixed set operations are far easier to read, and to get right, with explicit parentheses.
One quiet difference from a WHERE clause: duplicate elimination groups rows, and two NULLs group together. A NULL row in both blocks survives INTERSECT, even though NULL = NULL never evaluates to true in a comparison.
Example
-- Accounts that took a trial AND later paid. INTERSECT compares whole rows.
-- Requires MySQL 8.0.31 or later; present in 8.4 LTS.
SELECT account_id FROM trial_signups
INTERSECT
SELECT account_id FROM paid_subscriptions;
-- The same answer written as a join, which is how this was done before 8.0.31.
-- DISTINCT is required: a join emits one row per matching PAIR, so an account
-- with two trials and three subscriptions would appear six times.
SELECT DISTINCT t.account_id
FROM trial_signups AS t
JOIN paid_subscriptions AS p ON p.account_id = t.account_id;When to use it
- Find the accounts present in two different lists, such as trial users who also paid.
- Reconcile two feeds by checking which whole rows agree exactly.
- Replace a JOIN plus DISTINCT when you only want rows common to both sides.
- Check that a migration produced identical rows in the old and new table.
More examples
The same intersection three ways
All three return the emails on both lists. The set operator is the clearest when you are comparing entire rows of the same shape; the subquery forms win when you need columns from only one side, or when the code has to run on a release older than 8.0.31.
-- 1. Set operator (MySQL 8.0.31+). Says exactly what it means.
SELECT email FROM newsletter_subscribers
INTERSECT
SELECT email FROM webinar_attendees;
-- 2. IN subquery. Works on every MySQL 8 release.
SELECT DISTINCT email
FROM newsletter_subscribers
WHERE email IN (SELECT email FROM webinar_attendees);
-- 3. EXISTS. Same result, and usually the form to prefer over IN when the
-- inner column can be NULL or the inner query is correlated.
SELECT DISTINCT n.email
FROM newsletter_subscribers AS n
WHERE EXISTS (SELECT 1 FROM webinar_attendees AS w WHERE w.email = n.email);Multi-column reconciliation
Whole-row matching is where INTERSECT pays off. The join equivalent needs an ON clause naming all three columns, and each one has to be written twice — an easy place to drop a condition and silently widen the match.
-- Which (order_id, sku, qty) triples agree exactly between the shop's export
-- and the warehouse's feed? Every column must match, positionally.
SELECT order_id, sku, qty FROM shop_export
INTERSECT
SELECT order_id, sku, quantity FROM warehouse_feed;
-- Column names may differ between blocks; only position and count matter.
-- The result's column names come from the first block: order_id, sku, qty.Mixing operators, with the precedence made explicit
Use this when a query combines set operators. The parentheses cost nothing at run time and remove the one ambiguity in set-operation syntax that silently produces a wrong answer rather than an error.
-- Intent: customers who are active, minus those on both the fraud list
-- and the chargeback list.
SELECT id FROM customers WHERE status = 'active'
EXCEPT
(SELECT customer_id FROM fraud_flags
INTERSECT
SELECT customer_id FROM chargebacks);
-- Without the parentheses MySQL evaluates it the same way, because INTERSECT
-- binds tighter. Write them anyway: the next reader should not have to know
-- the precedence table to review the query.
Discussion