Snippet

Optimizing Existence Checks with EXISTS Instead of COUNT

Using WHERE EXISTS allows the database query execution engine to evaluate conditionally and stop scanning table records as soon as a single match is encountered, outperforming aggregate COUNT checks.

snippet.sql

sql

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Breakdown

SELECT c.customer_id, c.customer_name

Retrieves the primary identifier and name of targeted customers.

FROM customers c

Specifies the primary customer table with table alias c.

WHERE EXISTS (

Begins a conditional subquery existence check.

SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id

Returns true upon finding the first matching order record for the current customer, short-circuiting further evaluation.

);

Closes the subquery expression.

Previous snippet Next snippet

More Intermediate SQL exercises →

Optimizing Existence Checks with EXISTS Instead of COUNT

Related