SQL Joins Interview Questions: LEFT JOIN, Filters, and Duplicate Rows

1. What is the difference between INNER JOIN and LEFT JOIN?

An inner join returns matching combinations. A left join also preserves unmatched rows from the left side, with null values for right-side columns. PostgreSQL's tutorial illustrates this distinction. In an interview, explain which entities must remain in the report before choosing a join type.

Use this original sample: Ada has two orders, Ben has one pending order, and Cy has none. An inner join on customer ID produces three rows and omits Cy. A left join produces four rows, including Cy's unmatched row. Multiple orders are not accidental duplicates here: they follow from the one-to-many relationship.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy');
INSERT INTO orders VALUES (10, 1, 'paid'), (11, 1, 'paid'), (12, 2, 'pending');

2. Why can WHERE change a LEFT JOIN result?

Suppose you need every customer with a count of paid orders. Put the paid-order matching condition in the join so customers without paid orders remain. Moving that condition into WHERE removes rows whose right-side status is null, including unmatched customers. The two queries answer different questions.

For the query below, the expected rows are Ada: 2, Ben: 0, and Cy: 0. Ben has an order, but it does not satisfy the paid condition. Predict those rows before executing the query. Then move the status condition to WHERE and explain why only Ada remains.

SELECT c.name, COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

3. Why use COUNT(o.id) rather than COUNT(*) here?

The left join creates one preserved row even when no order matches. COUNT(*) counts that row; COUNT(o.id) counts only non-null order IDs. In this schema, order IDs are non-null primary keys, so counting them expresses the intended number of matched orders.

Do not mechanically replace every COUNT(*) in a report. First define what a row means at that point in the query. If you need the number of customers, order rows are the wrong unit. If you need the number of paid orders, a nullable column unrelated to order identity may undercount real matches. Tie the expression to the reporting contract.

4. Why does joining a third table inflate totals?

Imagine order 10 has two items and two payments. Joining orders to both child tables can produce four combinations for that order. Summing an order-level amount across those combinations overstates the total. DISTINCT is not a general repair because equal-looking values can represent legitimate separate events.

Explain the grain required in the final output. One approach is to aggregate each child table to order level before joining it to orders. Another is to query only the relationship needed for the question. Draw the tiny two-by-two example and calculate the expected total first; this exposes the mistake more clearly than staring at a large production query.

5. How do you find customers with no orders?

A correlated NOT EXISTS query expresses that no matching order exists. Another pattern is a left join followed by a null test on a non-null right-side key. Explain the null assumptions rather than using an arbitrary optional field to identify an unmatched row.

With the sample data, only Cy should be returned. Change the question to 'no paid orders' and both Ben and Cy qualify. That small wording change should alter the matching condition, not trigger a new query copied without understanding. Be especially careful with NOT IN when the compared values may contain nulls.

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
)
ORDER BY c.id;

A join debugging routine to practise

State the grain of each input, the relationship cardinality, and the desired output grain. Count rows before and after every join on a small filtered sample. Inspect unmatched keys and repeated keys separately. Then verify a business total against an independently calculated result rather than assuming a syntactically valid query is correct.

During interview preparation, ask Cluegent to change one assumption, such as allowing an order with no customer or adding partial payments. Predict the output before running it. This guide complements the broader SQL question page; use it when your weak point is explaining missing rows or inflated aggregates.

Where Cluegent helps

Cluegent supports permitted live workflows with transcript context, typed prompts, screenshot-aware answers, resume context, custom response behavior, quick action buttons, and a private desktop overlay. It is most useful when you already understand the subject and need help staying structured under pressure.

Frequently asked questions

Does LEFT JOIN always preserve every left-side row?

The join preserves unmatched left rows, but a later WHERE condition can filter them out. Consider the whole query, not the join keyword alone.

Should I fix inflated totals with DISTINCT?

Not automatically. Establish relationship cardinality and output grain; pre-aggregate child data where appropriate and verify against a small known result.