SQL
Filter Parent Records by Child Existence with EXISTS
Learn to efficiently filter parent records (e.g., customers) based on whether associated child records (e.g., orders) exist using the SQL EXISTS operator.
SELECT c.customer_id, c.customer_name, c.email
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= '2023-01-01'
AND o.status = 'completed'
);
-- To find customers with NO completed orders in 2023:
SELECT c.customer_id, c.customer_name, c.email
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= '2023-01-01'
AND o.status = 'completed'
);
How it works: This snippet demonstrates using `EXISTS` and `NOT EXISTS` with subqueries to efficiently filter records. The first query retrieves customers who have placed at least one completed order since '2023-01-01'. The second query finds customers who have *not* placed any such orders. `EXISTS` is often more performant than `JOIN` when you only need to check for existence and not retrieve data from the joined table.