SQL Server
INNER JOIN vs LEFT JOIN in SQL Server
Compare INNER JOIN and LEFT JOIN in SQL Server and learn when to keep unmatched customers, filter orders, and find customers with no orders.
On this page
In the previous lesson, you connected dbo.Customers to dbo.Orders through CustomerId. Now choose which rows should survive when one side has no match. Do you want only customer/order pairs that exist, or every customer, including people who have never placed an order? That is the practical choice between INNER JOIN and LEFT JOIN.
The demo relationship is one customer to many orders. Ana (CustomerId 1) has orders 1001, 1002, and 1007; Ben (2) has one, David (4) has two, and Grace (7) has two. The other six customers have none. These facts make the difference visible without inventing records.
INNER JOIN keeps matches
An INNER JOIN returns rows only when the ON condition finds a matching row on both sides:
SELECT
c.CustomerId,
c.FirstName,
c.LastName,
o.OrderId,
o.OrderDate,
o.Amount
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId;There are eight orders rows, and the foreign key ensures each refers to an existing customer, so this query returns eight rows. Ana appears three times and David twice because their orders are separate records. Customers without orders do not appear at all. The join has not removed those customers from the database; they simply do not meet the question this query asks: show matching customer/order pairs.
This is useful when the result is about orders that have a customer. For example, an order report that displays the customer’s name can use INNER JOIN when an order without a valid customer should not be reported as a complete pair. In this demo the foreign key makes every order match. In less constrained data, an unmatched order is excluded by an inner join.
LEFT JOIN keeps the left side
A LEFT JOIN keeps every row from the table written before the join. Matching right-side values are attached; when no right-side row matches, its selected columns are NULL:
SELECT
c.CustomerId,
c.FirstName,
c.LastName,
o.OrderId,
o.OrderDate,
o.Amount
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId;This query represents all ten customers across fourteen result rows: eight matched order rows plus six customers without orders. Customers 3, 5, 6, 8, 9, and 10 each appear once with NULL in the selected order columns. A customer with several orders still appears several times: preserving the left row does not collapse the matching right rows.
The order of the tables expresses the population to preserve. Customers LEFT JOIN Orders means “keep every customer.” Orders LEFT JOIN Customers means “keep every order,” even if a matching customer is absent. The latter is not equivalent in intent, even when a current foreign key guarantees all orders have a customer. Start with the entity whose rows must remain, then write it on the left.
| Join | What stays in the result? | If no row matches on the right |
|---|---|---|
INNER JOIN |
Matching pairs only | The left row is omitted |
LEFT JOIN |
Every left row, plus matching right rows | Right-side columns are NULL |
- INNER: matching customer + order → returned
- INNER: customer without order → excluded
- LEFT: every customer → returned
- LEFT: matching order → attached
- LEFT: no match → NULL order columns
Filtering after a LEFT JOIN
Consider wanting every customer while showing only shipped orders. This query looks plausible:
SELECT c.CustomerId, c.FirstName, o.OrderId, o.Status
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId
WHERE o.Status = N'Shipped';But WHERE filters the joined rows after matching. For a customer with no order, o.Status is NULL; the comparison to N'Shipped' is not true, so that row is removed. The result contains only customers with a shipped order. For this condition, the outer join no longer preserves customers without a qualifying order.
To keep every customer and attach only shipped orders, move the right-side condition into ON:
SELECT c.CustomerId, c.FirstName, o.OrderId, o.Status
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId
AND o.Status = N'Shipped';Now all ten customers remain; David has his shipped order 1004, and other customers have NULL order values unless they have a shipped match. ON decides which right rows count as a match; WHERE decides which completed result rows remain. If the actual goal is only shipped orders, using INNER JOIN with a WHERE filter states that intent more directly. Learn more about how NULL behaves in SQL Server.
Find customers with no orders
The unmatched right-side key is NULL, so test a non-nullable order column:
SELECT c.CustomerId, c.FirstName, c.LastName
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId
WHERE o.OrderId IS NULL;OrderId is the order primary key and cannot be NULL on a real stored order. Its NULL value here is supplied by the outer join to represent “no matching order.” The query returns customers 3, 5, 6, 8, 9, and 10. This pattern is often called an anti-join: find left rows without a related right row.
Choose based on the question
Use INNER JOIN for “show orders with customer names” or “show customers who have placed an order.” Use LEFT JOIN for “show every customer and any orders they placed.” Use LEFT JOIN plus WHERE o.OrderId IS NULL for “which customers have never ordered?” State the desired rows before selecting the join type; the keyword follows from the result you need.
Common errors are using INNER JOIN when unmatched left rows matter, filtering a right-side column in WHERE without considering NULL, joining columns that are not the relationship keys, and assuming LEFT JOIN produces one row per customer. Also check which table is on the left: that is the side preserved. RIGHT JOIN and FULL OUTER JOIN exist, but these everyday questions are usually clearest with INNER or LEFT.
Predict the rows before you run the query
A useful habit is to write down which entities should appear, then check the query against that list. For the INNER JOIN, predict only customers that have at least one order; in this dataset those are Ana, Ben, David, and Grace. Ana has three orders, David has two, and Ben has one and Grace has two, so the result has eight rows. For the LEFT JOIN from Customers, predict all ten names; six customers have no related order and will have NULL order columns. The result has fourteen rows because the four matched customers contribute eight order rows. Writing both the entities and expected row count makes it easier to notice a wrong join key or a misplaced filter.
This prediction also clarifies what “matching” means. The join condition checks equality between customer identifiers, not whether names happen to resemble one another or whether customers live in the same city. Each order row has one CustomerId, and the foreign key links it to one customer row. Several orders can use that same identifier, so each valid order contributes another matching pair. A join returns pairs, not a merged record that overwrites one side.
In a report, ask what should happen to customers with no order before choosing the join. A sales team might want an order-detail list, where a customer without an order has nothing to contribute; an INNER JOIN is natural. A customer service team may need a roster that still shows every customer while leaving order fields empty; use LEFT JOIN. A follow-up list for outreach may need only the unmatched customers; LEFT JOIN plus the NULL-key test answers that question. The same tables support different results because the business question differs.
If a LEFT JOIN result is unexpectedly smaller than the left table, inspect filters on right-side fields in WHERE first. If it is larger than expected, look for multiple valid right-side matches, which may be correct, or verify that the ON condition uses the intended keys. Do not assume that changing INNER to LEFT will fix an incorrect condition. Join type controls which unmatched rows survive; the ON condition controls what counts as a match. They solve separate problems.
For a reliable check, compare the result with the key population, not only with a displayed name. Names need not be unique: two customers could share one, while CustomerId is designed to identify a single row. Count distinct customer identifiers when the question concerns customer coverage, and inspect OrderId when the question concerns orders. A LEFT JOIN can show all customers while repeating some identifiers because those customers have multiple orders. Reading the identifiers prevents the mistaken conclusion that those rows disappeared or were duplicated accidentally.
Use a non-nullable key for the unmatched-row test. If you test a nullable order field such as a note or optional reference, a real matching order could itself contain NULL, making it look like no order exists. OrderId avoids that confusion because every stored order must have its primary key. This small choice makes the anti-join pattern dependable.
Practice
Try these before reading the answers: (1) list matching orders and customer names; (2) list every customer and any order ID; (3) find customers without orders; (4) keep every customer but attach only Complete orders; (5) list only orders over $100. The current dataset has Complete, Pending, and Shipped statuses.
Answers
-- 1
SELECT c.FirstName, c.LastName, o.OrderId
FROM dbo.Customers AS c INNER JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId;
-- 2
SELECT c.CustomerId, c.FirstName, o.OrderId
FROM dbo.Customers AS c LEFT JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId;
-- 3
SELECT c.CustomerId, c.FirstName
FROM dbo.Customers AS c LEFT JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId
WHERE o.OrderId IS NULL;
-- 4
SELECT c.CustomerId, c.FirstName, o.OrderId, o.Status
FROM dbo.Customers AS c LEFT JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId AND o.Status = N'Complete';
-- 5
SELECT OrderId, Amount FROM dbo.Orders WHERE Amount > 100.00;Next, understand why one customer can appear on several join rows. The Foundations roadmap lists the next lesson, while JOINs Explained reviews the shared-key idea.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 10 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetQuery Store Quick ReferenceUse SQL Server Query Store to find regressions, compare plans, analyze runtime history, force or unforce plans safely, and understand when Query Store data can mislead you.Open cheat sheet
- RoadmapSQL Server FoundationsCompare INNER JOIN and LEFT JOIN in SQL Server and learn when to keep unmatched customers, filter orders, and find customers with no orders.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.