Find Rows Common To Two Queries With INTERSECT

Return only the rows that appear in both result sets using `INTERSECT`.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find customers who appear in both the previous and current year

Alice and Carol appear in both tables. Bob is only in prev_customers and Dave is only in curr_customers, so neither appears in the intersection. Use EXCEPT to find customers who were in the previous year but not the current one.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE prev_customers (id INT, name VARCHAR(50));

CREATE TABLE curr_customers (id INT, name VARCHAR(50));

INSERT INTO
  prev_customers (id, name)
VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Carol');

INSERT INTO
  curr_customers (id, name)
VALUES
  (1, 'Alice'),
  (4, 'Dave'),
  (3, 'Carol');

Validated query Shared across supported engines.

SQL

SELECT
  name
FROM
  prev_customers
INTERSECT
SELECT
  name
FROM
  curr_customers
ORDER BY
  name;

Expected result Returned rows for the shared example.

name
Alice
Carol

Output is identical across all engines.

Useful when

Where this command helps.

finding records common to two separate queries or time periods
identifying items that appear in multiple datasets

Explanation

What the command is doing.

INTERSECT returns the rows that are present in both the left and right query results, removing duplicates. Both queries must return the same number of columns with compatible types. It is the SQL equivalent of a set intersection. An INNER JOIN on the same columns produces a similar result but can include duplicates if matching rows appear multiple times.

Find Rows Common To Two Queries With INTERSECT

Find customers who appear in both the previous and current year

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.