How do SQL EXISTS statements work?

sql

subquery

performance

best-practices

byAlex Kataev·Sep 23, 2024

The EXISTS operator in SQL is used to verify if any rows are returned by a subquery, producing either TRUE or FALSE. It's typically employed in a WHERE clause to filter rows from the main query based on the existence of linked records in another table.

For instance:

SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.manager_id = e.id
    -- "Finds the bosses hiding in the departments!"
);

This code fetches all the employees who are managers in one or more departments. The SELECT 1 is a common practice that efficiently checks for existence without requiring the subquery to return any actual data.

Diving into EXISTS

The EXISTS operator acts as a booleometer, scanning over the data rows for a match. It simply checks for presence, without considering quantity.

Correlation with subqueries

A correlated subquery is often used with EXISTS, where each data row is checked individually. Think of it as an 'each-one-check-one' system.

Subquery execution strategy

Contrary to common assumptions, the EXISTS operator doesn't cause the entire subquery to be executed. It's like 'taking a peek and scooting'. As soon as it finds the first matching row, it stops, saving computational resources and time.

Examples of use-cases

Avoiding duplicate records
Efficient checking of large datasets
Cleaner expression of query requirements

EXISTS vs. IN vs. JOIN

When SELECT fields are irrelevant

One attractive quirk of EXISTS is that it doesn't give two hoots about the specific fields that you select; all it cares about is the existence of any row at all! You could SELECT 1, or a specific field like SELECT department_id, yet the result would remain unaltered.

EXISTS vs. IN

In cases where large result-sets are being handled, EXISTS may outperform IN, as it leaves the party early once it finds a match, while IN obediently waits till the very end.

EXISTS vs. JOIN

Remember to consider the risk of duplicate records while using a JOIN. It's like inviting everyone to a party and ending up with gatecrashers. On the flip side, EXISTS ensures a strict 'invite-only' policy. Using DISTINCT with JOIN can prevent duplication, but EXISTS does the job natively.

More use-cases and considerations

Using JOIN and INTERSECT

JOIN and INTERSECT can be used similarly for checking data links but can heavily impact performance and output, especially with larger tables.

'NOT EXISTS' to exclude rows

NOT EXISTS, the evil twin of EXISTS, filters out rows for which the subquery records a match. It's the gatekeeper who stops you if your name's on the black-list!