IN Operator: Customers Who Placed Orders

basic

Marketing wants list of customers who have placed at least one order.

SELECT

customer_id,

name,

email

FROM customers

WHERE customer_id IN (

SELECT customer_id

FROM orders

);

-- Subquery returns list of customer IDs who have orders

-- IN checks if each customer_id is in that list

customer_id | name | email

1 | John | john@email.com

2 | Jane | jane@email.com

5 | Alice | alice@email.com

IN operator checks membership. Subquery executes once, returns list of customer IDs from orders table. Main query filters customers to only those whose ID appears in the list. Simple and readable for small to medium result sets.

All

EXISTS Operator: Same Query, Better Performance

basic

Same requirement but using EXISTS for better performance with large datasets.

SELECT

c.customer_id,

c.name,

c.email

FROM customers c

WHERE EXISTS (

SELECT 1

FROM orders o

WHERE o.customer_id = c.customer_id

);

-- For each customer, check if any orders exist

-- Stops at first match (efficient!)

customer_id | name | email

1 | John | john@email.com

2 | Jane | jane@email.com

5 | Alice | alice@email.com

EXISTS is correlated - executes per customer but stops at first matching order. More efficient than IN for large datasets because it doesn't need to build complete list. Needs index on orders(customer_id) for performance.