SQL EXISTS vs JOIN: Filter Without Multiplying Rows

You need customers with paid orders, but one customer appears several times. The data may not be duplicated: a JOIN can produce one row for every matching order. When the requirement is only about existence, EXISTS often expresses the intent more clearly.

1. Define what one result row represents

Assume customers has a primary key id, and orders contains id, customer_id and status. In an illustrative dataset, An has two paid orders, Binh has one pending order and Chi has no orders.

The business question is which customers have at least one paid order. Each output row should therefore represent a customer, not an order. Establish this before choosing SQL syntax.

2. Why does JOIN return multiple rows?

SELECT c.id, c.name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid';

An appears twice because two customer–order pairs match. This follows PostgreSQL's JOIN rules; it does not prove that customers contains duplicate records.

If the screen displays individual orders, select o.id and the required order fields. Two rows for An are then correct. Trouble arises when the application treats each row as a distinct customer and counts or paginates accordingly.

3. Use EXISTS to require at least one match

SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
      AND o.status = 'paid'
)
ORDER BY c.id;

EXISTS tests whether the subquery returns any rows. An appears once; Binh and Chi are excluded. SELECT 1 communicates that the outer query does not need order contents as its result.

The correlation o.customer_id = c.id matters. Without it, the subquery only asks whether any paid order exists anywhere. If one does, every customer can pass the filter.

4. DISTINCT does not replace understanding relationships

SELECT DISTINCT c.id, c.name can also produce the desired customer list from the original join. However, adding o.id makes those rows different again, so DISTINCT no longer collapses them.

Neither DISTINCT is universally wrong nor EXISTS universally faster. Choose an expression that matches the requirement, then evaluate its plan with representative data. Order counts, status distribution and indexes can all affect performance.

5. No paid orders is different from no orders

SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
      AND o.status = 'paid'
)
ORDER BY c.id;

The expected result includes Binh and Chi. Binh has an order but no paid orders; Chi has none at all. To require that a customer has no orders whatsoever, remove the status condition from the subquery.

Do not replace “has no paid orders” with “has an order that is not paid.” A customer with both a paid and a pending order satisfies the second condition but not the first. This is a business distinction, not a SQL style preference.

6. Testing checklist

• No orders: excluded by EXISTS with paid, included by NOT EXISTS with paid.
• One paid order: returned once.
• Multiple paid orders: still returned once by EXISTS.
• Both paid and pending orders: included by EXISTS with paid, excluded by NOT EXISTS with paid.
• Tenant and authorization boundaries are applied according to the data model, not replaced by status filtering.

These are expected outcomes for an illustrative dataset, not benchmark results. Test with fixtures in your project and ensure count and pagination queries use equivalent conditions. Add or change indexes only after reviewing the plan and write costs.

Use JOIN to combine rows for their data, and EXISTS to filter outer rows by the presence of related data. Defining what each result row means prevents many counting, filtering and pagination mistakes.