Basic EXISTS - Customers with Orders

basic

Find customers who have placed at least one order

-- Semi-join using EXISTS

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

)

ORDER BY c.customer_name;

-- Alternative with INNER JOIN (less efficient for filtering)

SELECT DISTINCT c.customer_id, c.customer_name, c.email

FROM customers c

INNER JOIN orders o ON c.customer_id = o.customer_id

ORDER BY c.customer_name;

Returns customers who have placed orders

EXISTS checks if subquery returns any rows. More efficient than JOIN when you only need to filter, not combine data.

All

Complex EXISTS with Conditions

intermediate

Find customers who have placed high-value orders (>$1000) in the last 6 months

-- EXISTS with multiple conditions in subquery

SELECT

c.customer_id,

c.customer_name,

c.customer_tier,

c.total_lifetime_value

FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.customer_id = c.customer_id

AND o.total_amount > 1000

AND o.order_date >= CURRENT_DATE - INTERVAL 6 MONTH

AND o.status = 'completed'

)

ORDER BY c.total_lifetime_value DESC;

Returns customers with recent high-value completed orders

EXISTS subquery can have complex WHERE conditions. Efficiently filters customers based on order criteria.

All