SQL
Efficiently Checking Existence with SQL EXISTS and NOT EXISTS
Determine if related records exist without needing to join tables. Use EXISTS/NOT EXISTS for powerful and optimized conditional data retrieval.
-- Find customers who have placed at least one order
SELECT
c.customer_id,
c.first_name,
c.last_name
FROM
customers c
WHERE
EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
);
-- Find customers who have NOT placed any orders in the last 90 days
SELECT
c.customer_id,
c.first_name,
c.last_name
FROM
customers c
WHERE
NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
);
How it works: The `EXISTS` and `NOT EXISTS` operators are highly efficient for checking the presence or absence of related records without the overhead of a full join. `EXISTS` returns true if the subquery returns any rows, while `NOT EXISTS` returns true if the subquery returns no rows. These are particularly useful for filtering main query results based on complex conditions in related tables, such as finding customers who have or haven't made a purchase within a specific timeframe.