SQL
Filter Parent Records Using EXISTS with a Subquery
Optimize SQL queries by filtering parent records based on the existence of related child records using the EXISTS clause. Often more efficient than IN for large datasets.
SELECT p.product_id, p.product_name
FROM products p
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.product_id = p.product_id
AND o.order_date >= '2023-01-01'
);
How it works: This snippet demonstrates how to use the `EXISTS` clause to filter parent records based on the existence of related child records that meet certain criteria. The outer query selects products, but only if there is at least one corresponding order for that product placed on or after '2023-01-01'. `EXISTS` stops scanning as soon as it finds a match, making it generally more efficient than `IN` for large subquery results because it doesn't need to return actual data, just a boolean true/false.