A JOIN returns matching row combinations, not automatically one row per customer or one row per order. Before writing the query, state the grain of each input and the grain required in the result. That step explains many “duplicate row” interview questions.

The examples use SQL Server syntax and fictional data held in table variables. They do not access an application's customer database.

Why can one customer appear several times?

DECLARE @Customers TABLE (CustomerId int PRIMARY KEY, Name varchar(20));
DECLARE @Orders TABLE (OrderId int PRIMARY KEY, CustomerId int, Status varchar(10));
INSERT @Customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen');
INSERT @Orders VALUES (10, 1, 'ready'), (11, 1, 'held'), (12, 2, 'ready');

SELECT c.CustomerId, o.OrderId
FROM @Customers AS c
JOIN @Orders AS o ON o.CustomerId = c.CustomerId
ORDER BY c.CustomerId, o.OrderId;

The result contains (1,10), (1,11) and (2,12). Customer 1 appears twice because two different orders match. Those are not duplicate order rows. They are the expected result of the one-to-many relationship.

How do you keep customers with no orders?

A left outer join keeps rows from its left input even when no right-side row matches. The right-side columns are then null for that unmatched result. In the sample, customer 3 needs that treatment.

-- Continue in the same batch, where the table variables exist.
SELECT c.CustomerId, o.OrderId
FROM @Customers AS c
LEFT JOIN @Orders AS o
  ON o.CustomerId = c.CustomerId AND o.Status = 'ready'
ORDER BY c.CustomerId, o.OrderId;

The rows are (1,10), (2,12) and (3,NULL). The status condition helps decide which order rows can match. It does not remove the preserved customer when no ready order exists.

What if the status condition moves into WHERE?

A condition requiring o.Status = 'ready' in WHERE filters the joined result. The unmatched customer's null status does not satisfy that condition, so the customer disappears. This is a change in meaning, not a cosmetic query rewrite.

There are valid queries that filter after joining. The important question is whether the required output should keep unmatched left rows. Choose the location of the condition from that requirement.

Should DISTINCT be used to fix repeated rows?

Only if removing duplicate selected values is the actual required operation. Adding DISTINCT without understanding the relationship can hide a missing condition or collapse meaningful order rows. It also does not explain the desired output grain.

If the caller needs one row per customer, consider an aggregation or an existence test that expresses that purpose. If the caller needs each order, repeated customer identifiers are normal. Say which output is intended.

How do you check row counts?

Use a small dataset with a customer having two orders, a customer having one and a customer having none. Predict the rows before running the query. Then test a filter that matches no right-side rows at all.

Be careful with counts after an outer join. Counting every output row and counting a non-null matched order identifier answer different questions. A customer with no order still has a preserved joined row.

Practical interview follow-ups

  • What result grain does the caller need?
  • Which columns identify a unique input row?
  • Should customers with no matching order remain?
  • Is a repeated value meaningful or evidence of an incorrect join?

For your next exercise, add a second ready order for customer 1. Explain why the second query now returns another row, then write a different query for “customers with at least one ready order.” Keep those two questions separate instead of repairing both with DISTINCT.

References and next practice

learn.microsoft.com — sql join · learn.microsoft.com — sql select

sql question collection · Browse interview topics · Practise a question in Interview Challenge · How to Answer Technical Interview Questions Out Loud