Find Rows In One Query But Not Another With EXCEPT

Return rows from the first query that do not appear in the second query using `EXCEPT`.

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

Docker-validated Not currently validation-green

Example 1

List products that have not been discontinued

Trackball appears in discontinued_products and is excluded. Keyboard and Mouse appear only in all_products and are returned. Use INTERSECT to find products that appear in both tables.

Source table data Rows loaded before the example query runs.

Setup

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

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

INSERT INTO
  all_products (id, name)
VALUES
  (1, 'Keyboard'),
  (2, 'Mouse'),
  (3, 'Trackball');

INSERT INTO
  discontinued_products (id, name)
VALUES
  (3, 'Trackball');

Validated query Shared across supported engines.

SQL

SELECT
  name
FROM
  all_products
EXCEPT
SELECT
  name
FROM
  discontinued_products
ORDER BY
  name;

Expected result Returned rows for the shared example.

name
Keyboard
Mouse

Output is identical across all engines.

Useful when

Where this command helps.

finding records present in one dataset but absent from another
identifying churned customers or removed items

Explanation

What the command is doing.

EXCEPT returns distinct rows from the left query that have no match in the right query — the SQL equivalent of a set difference. Both queries must return the same number of columns with compatible types. It is useful for finding missing records, dropped items, or anything present in one dataset but absent from another.

Find Rows In One Query But Not Another With EXCEPT

List products that have not been discontinued

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.