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

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

1. Return every customer and the number of orders they have placed, including customers with zero orders.

   Your answer Try it in the playground →
   Reveal a model answer

   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

   Customers is the base table and the LEFT JOIN keeps the four who never ordered. Count o.order_id, not *: the unmatched row still exists, so count(*) would give those customers 1 instead of 0.

2. List each shipped order with its customer name and order total.

   Your answer Try it in the playground →
   Reveal a model answer

   select o.order_id,
          c.first_name || ' ' || c.last_name as customer_name,
          sum(oi.quantity * oi.unit_price) as order_total
   from orders as o
   join customers as c on c.customer_id = o.customer_id
   join order_items as oi on oi.order_id = o.order_id
   where o.status = 'shipped'
   group by o.order_id, customer_name
   order by o.order_id

   The total lives on the line items, so the join fans each order out to one row per item, and GROUP BY order_id folds it back to one row per order. Say the grain out loud: one row per shipped order.

3. Find products that have never appeared in an order item.

   Your answer Try it in the playground →
   Reveal a model answer

   select p.product_id, p.product_name
   from products as p
   left join order_items as oi on oi.product_id = p.product_id
   where oi.product_id is null
   order by p.product_id

   An anti-join: keep every product, then keep only those where the right side found nothing. NOT EXISTS answers it too, and says so more directly. Avoid NOT IN against order_items unless you know product_id is never null there.

4. Explain why a customer count becomes too high after joining orders to order_items.

   Your answer
   Reveal a model answer

   The join repeats each order once per line item, so each customer appears once per line item they bought. count(*) now counts line items. Count the entity you mean, count(distinct c.customer_id), or leave order_items out when the question does not need it.

5. Given rentals, customers, and movies, return who rented what, sorted by rental date.

   Your answer Try it in the playground →
   Reveal a model answer

   select r.rental_date,
          c.first_name,
          c.last_name,
          m.title
   from rentals as r
   join customers as c on c.customer_id = r.customer_id
   join movies as m on m.movie_id = r.movie_id
   order by r.rental_date, r.rental_id

   Start from the fact table, rentals, and join each lookup on its key. Add rental_id as a tie-breaker so two rentals on one day come back in the same order every time.

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.