Avoid Duplicate Parent Rows With EXISTS

Use `EXISTS` when you only need to test whether a related row exists, not return one output row per match.

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

Docker-validated Not currently validation-green

Example 1

Return each customer once even when many orders exist

Alice has two matching orders, but she still appears once because EXISTS only checks whether at least one related row exists. Bob also appears once. Carol has no orders, so she is excluded. A plain join would return Alice twice unless you added extra deduplication.

Source table data Rows loaded before the example query runs.

Setup

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

CREATE TABLE orders (id INT, customer_id INT);

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

INSERT INTO
  orders (id, customer_id)
VALUES
  (1, 1),
  (2, 1),
  (3, 2);

Validated query Shared across supported engines.

SQL

SELECT
  name
FROM
  customers c
WHERE
  EXISTS (
    SELECT
      1
    FROM
      orders o
    WHERE
      o.customer_id = c.id
  )
ORDER BY
  name;

Expected result Returned rows for the shared example.

name
Alice
Bob

`EXISTS` is used here specifically to avoid duplicate customer rows.

Useful when

Where this command helps.

preventing duplicate parent rows in relationship checks
replacing joins used only as existence filters

Explanation

What the command is doing.

A frequent join mistake is using an inner join for a pure existence test. If the child table has multiple matching rows, the parent row appears multiple times. EXISTS avoids that because it answers only yes-or-no for each parent row. This is often the cleanest way to express 'show customers who have at least one order' without deduplication cleanup.

Avoid Duplicate Parent Rows With EXISTS

Return each customer once even when many orders exist

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.