Basic EXISTS Syntax with Correlated Subqueries
You'll learn: How to write EXISTS clauses that check whether related rows exist in another table by referencing the outer query.
What is EXISTS with Correlation?
Think of EXISTS like asking "Does this thing have any matching partners?" Instead of retrieving data, EXISTS simply returns TRUE or FALSE based on whether the subquery finds any rows. When combined with correlation, the subquery references columns from the outer query, creating a row-by-row check.
The beauty of EXISTS is efficiency: it stops searching as soon as it finds one matching row—it doesn't need to count them all.
Basic Syntax Pattern
SELECT column1, column2
FROM outer_table o
WHERE EXISTS (
SELECT 1
FROM inner_table i
WHERE i.foreign_key = o.primary_key
AND i.some_condition = 'value'
);
Notice how the subquery references o.primary_key from the outer table. This correlation makes the subquery execute once per row of the outer query.
Practical Example
Find all customers who have placed at least one order:
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
For each customer, the subquery checks: "Are there any orders with this customer_id?" If yes, include that customer in results.
The SELECT 1 is a convention—EXISTS only cares if rows exist, not what data they contain.
Key Takeaway: EXISTS with correlated subqueries lets you filter rows based on the existence of related data in another table, referencing outer query columns to create dynamic, row-specific checks that stop at the first match for efficiency.