sql / intermediate
Snippet
Filtering Records with Correlated EXISTS Clauses
A correlated subquery references columns from the outer query. The EXISTS operator evaluates to true as soon as the inner query finds at least one matching row, making it an efficient way to test for relationship presence without producing duplicate outer rows.
snippet.sql
sql
1
2
3
4
5
6
7
8
SELECT c.customer_id, c.company_nameFROM customers cWHERE EXISTS (SELECT 1FROM orders oWHERE o.customer_id = c.customer_idAND o.order_amount > 5000);
Breakdown
1
SELECT c.customer_id, c.company_name
Specifies the columns to retrieve from the primary customer table.
2
FROM customers c
Designates the main table and aliases it as 'c' for referencing inside the subquery.
3
WHERE EXISTS (
Evaluates a subquery condition, returning true as soon as a matching inner row is found.
4
SELECT 1
Selects a constant value inside the EXISTS subquery, since only row existence matters.
5
FROM orders o
Specifies the target order table to check for related records.
6
WHERE o.customer_id = c.customer_id
Correlates the inner order record with the outer customer ID.
7
AND o.order_amount > 5000
Applies an additional filtering condition on the related order amount.