IN vs EXISTS in SQL Subquery Filtering

Question

You have `customers` and `orders`. Write a query to find all customers who have placed at least one order with `status = 'shipped'`. Explain whether `EXISTS` or `IN` is more robust against NULL values.

Accepted Answer

Optimal Solution:
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 
  FROM orders o 
  WHERE o.customer_id = c.customer_id 
    AND o.status = 'shipped'
);

Technical Explanation:
`EXISTS` performs a semi-join and stops scanning as soon as the first matching order is found. Unlike `NOT IN`, `EXISTS` is not vulnerable to NULL comparison traps.

customer_id	name
1	Aarav
2	Bhavna
3	Chirag