Snippet

Optimizing Subquery Filtering using the EXISTS Operator

The EXISTS operator performs short-circuit evaluation, returning TRUE as soon as the query engine finds a single matching record in the subquery. This is significantly more efficient for large datasets compared to using IN with subqueries.

snippet.sql

sql

SELECT
  c.customer_id,
  c.company_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_status = 'COMPLETED'
);

Breakdown

FROM customers c

Assigns an alias to the outer table to enable correlation within the subquery.

WHERE EXISTS (

Evaluates truthiness based on early matching without building full result sets.

SELECT 1

Returns a constant scalar value since EXISTS only checks row existence, not actual column values.

WHERE o.customer_id = c.customer_id

Correlates each outer customer row with matching order records.

Previous snippet Next snippet

More Intermediate SQL exercises →

Optimizing Subquery Filtering using the EXISTS Operator

Related