SQL JOIN Interview Questions

JOIN questions test whether you can reason about table relationships, not just write join syntax. Interviewers want to hear which table is the base, which key connects the tables, and whether unmatched rows should remain in the result.

Start every join answer by naming the result grain. If the result should be one row per customer, do not let order or line-item rows accidentally change that grain. If the result should show every order line, a one-to-many join is expected.

Common JOIN interview prompts

1. Return every customer and the number of orders they have placed, including customers with zero orders.
2. List each shipped order with its customer name and order total.
3. Find products that have never appeared in an order item.
4. Explain why a customer count becomes too high after joining orders to order_items.
5. Given rentals, customers, and movies, return who rented what, sorted by rental date.

select c.customer_id,
       c.first_name,
       c.last_name,
       count(o.order_id) as orders
from customers as c
left join orders as o on o.customer_id = c.customer_id
group by c.customer_id, c.first_name, c.last_name
order by orders desc, c.customer_id

What matters

The first question needs a LEFT JOIN because customers with no matching orders still belong in the output. count(o.order_id)counts only matched orders; count(*) would count the preserved customer row even when no order exists.

The more advanced version asks why totals inflate. Joining orders to order_items repeats each order once per line item. That is correct when the report is about line items, but wrong when the report is supposed to count orders or customers.

select o.order_id,
       count(*) as joined_rows
from orders as o
join order_items as oi on oi.order_id = o.order_id
group by o.order_id
order by joined_rows desc

How to talk it through

Talk through the grain first: "I want one row per customer, so customers is my base table. I need a left join to keep customers without orders. I will count order_id, not star, because the unmatched left-join row should count as zero."

Then add a quick check. Count rows before and after the join, inspect a few customers with multiple orders, and confirm whether a one-to-many join is expected. That habit is often more impressive than writing the final query immediately.

Practice next

Work through joining customers to orders, INNER vs LEFT JOIN, and the inflated counts after a JOIN mistake page. For a fresh dataset, try the movie rentals join lesson.