SQL Server / Basic / Lesson 4

Join relationships and test existence

Combine customer and order facts without losing optional relationships or multiplying totals accidentally. Distinguish matching rows from the simpler question of whether a match exists.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Predict join cardinality from keys.
Keep unmatched rows with LEFT JOIN correctly.
Use EXISTS and NOT EXISTS for presence and absence.

Predict the number of rows

A join combines rows according to a predicate; it does not automatically produce one row per customer. If a customer has three orders, joining that customer to orders produces three matched rows. If each order then joins four lines, the result can have twelve rows. Write the intended grain beside every intermediate query before summing or counting anything.

Use keys for relationships rather than display names. Two customers can share a name, and a name can change. Qualify columns with readable aliases to avoid ambiguity. An INNER JOIN returns matching combinations only. A CROSS JOIN returns all combinations and is useful for deliberately building a small calendar or test matrix, but an accidental missing join predicate can create a huge result.

Read inner and outer results

The example gives customer 1 two orders and customer 2 none. LEFT JOIN preserves both customers, using NULL for order columns when no match exists. The three displayed rows are expected, not duplicate customer data needing DISTINCT. Removing the order identifier would hide the grain without changing it. Explain each output row from an input relationship.

A nullable output from the right side can mean an unmatched row, but it could also be a genuinely nullable stored attribute. Test a right-side non-null key when identifying missing matches. For several optional relationships, reason about each join independently; an INNER JOIN later in the chain can remove rows you expected an earlier outer join to retain.

sql Two customers and an optional order relationship

WITH c AS (SELECT * FROM (VALUES (1,N'Aya'),(2,N'Omar'))v(CustomerId,Name)),
 o AS (SELECT * FROM (VALUES (10,1),(11,1))v(OrderId,CustomerId))
SELECT c.CustomerId,c.Name,o.OrderId
FROM c LEFT JOIN o ON o.CustomerId=c.CustomerId
ORDER BY c.CustomerId,o.OrderId;

Expected output

CustomerId Name OrderId
1          Aya  10
1          Aya  11
2          Omar NULL

Customer 1 appears once for each match. Customer 2 receives one null-extended row because no order matches.

Place optional-side filters deliberately

For a report of every customer with any open orders attached, put the open-order predicate in the ON clause of the LEFT JOIN. Putting o.Status = N'Open' in WHERE removes unmatched customers because their status is NULL and the comparison is UNKNOWN. These two queries answer different questions even though both contain LEFT JOIN.

The ON clause defines which pairs match; WHERE filters the resulting rows. This distinction is especially important for tenant restrictions, effective dates, and soft-deleted child rows. Do not move a condition merely to make a query look shorter. First state whether a parent without a qualifying child should remain visible, then place the predicate to implement that decision.

Ask existence questions directly

EXISTS checks whether a correlated subquery finds any row. The selected constant is immaterial to the existence decision. Because the outer customer row is not multiplied by its orders, EXISTS is a clear choice for customers who have ordered. NOT EXISTS expresses customers with no matching order and avoids the NULL trap of NOT IN.

The example returns customer 2. Adding a nullable unrelated value inside the order row would not change the correlation decision. For an absence condition, the correlation itself must still be correct: forgetting the customer comparison asks whether the entire orders source is empty. Read the subquery aloud as “an order for this customer” rather than merely “an order.”

sql Find the customer without an order

WITH c AS (SELECT * FROM (VALUES (1),(2))v(CustomerId)),
 o AS (SELECT * FROM (VALUES (10,1),(11,1))v(OrderId,CustomerId))
SELECT c.CustomerId FROM c
WHERE NOT EXISTS (SELECT 1 FROM o WHERE o.CustomerId=c.CustomerId)
ORDER BY c.CustomerId;

Expected output

CustomerId
2

Two matches for customer 1 still mean existence. Customer 2 has none and survives NOT EXISTS.

Validate joins with tiny counterexamples

Construct test data containing a parent with zero children, one with one child, and one with several children. Add two parents with the same display name and, where permitted, a missing optional attribute. These cases expose incorrect join keys and accidental inner-join behavior much faster than inspecting a large realistic grid. Compare expected key sets and multiplicities, not only row counts.

If totals inflate after adding a second detail table, aggregate each detail source to the required parent grain before joining the summaries. DISTINCT can remove repeated projections while concealing the underlying mistake; it is not a general repair for a bad relationship. A correct join has a stated grain, a key-based predicate, and an explanation of unmatched rows.

Common errors and fixes

The multi-part identifier could not be bound.

Why it happens: A column uses an alias not present in that query scope.
How to fix it: Correct the alias and qualify each relationship consistently.
Prevent it next time: Use short, stable aliases and review scope.

Customers without orders disappeared.

Why it happens: A WHERE predicate rejects the NULL side of LEFT JOIN.
How to fix it: Move the qualifying-child condition to ON when parents must remain.
Prevent it next time: State the unmatched-parent requirement first.

Order amounts are counted repeatedly.

Why it happens: A one-to-many join changed the result grain.
How to fix it: Aggregate at order grain before joining further details.
Prevent it next time: Track multiplicity after every join.

Check your understanding

1. A customer has two orders and each order has three lines. How many inner-join rows result?

Show worked solution

Six line-grain rows. Summing an order-header total over them counts each order three times. Aggregate line amounts by order, or aggregate the header independently, before combining results.

2. Keep all customers but attach only orders from January. Where does the date test go?