SQL Server
SQL JOINs Explained in SQL Server
Learn SQL Server INNER JOIN and LEFT JOIN with Customers and Orders. Understand keys, aliases, ON conditions and customers without orders.
On this page
A customer can place many orders. dbo.Customers stores each customer once; dbo.Orders stores each order and its CustomerId. That key connects the tables. A JOIN combines related rows so a result can show a customer beside an order. In JaviBuildsLab the relationship is one customer to many orders. This is the first lesson in Combining Data, following NULL.
Why JOIN exists
Customer details and order facts have different grains: one row per customer versus one row per order. Storing a customer’s name on every order would repeat data and make corrections harder. CustomerId is the primary key in Customers and a foreign key in Orders. One customer can have many orders; the key connects them.
Keeping those facts in separate tables also makes updates safer. If Ana changes her name, one customer row needs correction rather than every order she has ever placed. Each order still records its owner through CustomerId. This is a one-to-many relationship: one row on the customer side can correspond to several rows on the order side. The primary key identifies a customer; the foreign key records that relationship. A JOIN uses this shared identifier to place the related facts together for a particular question.
- Customers — CustomerId (PK)
- 1 customer → many orders
- Orders — CustomerId (FK)
INNER JOIN: matching rows
An INNER JOIN returns a row for each matching customer-order pair:
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;Customers is the left table and Orders the right table. ON states the matching rule: same CustomerId. Only pairs with a match appear. The eight sample orders therefore produce eight rows, including three rows for Ana and two for David.
The query does not merge the tables permanently or change their stored rows. It creates a result for this request. Each selected column is qualified with an alias: c.CustomerId comes from Customers, while o.OrderId comes from Orders. You can add more columns when they answer the question, such as o.Status, or leave them out to keep the result focused. Because all eight orders refer to existing customers, every order has a match in this demo. In a database without an enforced foreign key, an INNER JOIN would omit an order whose CustomerId had no customer row; the join type determines which unmatched side disappears.
LEFT JOIN: keep every customer
A LEFT JOIN keeps every row from the left table, adding matching order columns where available:
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;Ten customers appear. Customers 3, 5, 6, 8, 9 and 10 have no order, so the order columns show NULL. This is useful when you need to find customers who have not ordered.
The LEFT JOIN begins with the left-hand population: customers. It looks for matching order rows and preserves a customer even when none is found. SQL Server fills the selected order columns with NULL for those unmatched rows. That NULL does not mean the customer record itself is missing; it means there was no related order row to supply those values. This is a common way to find customers without orders: keep the LEFT JOIN and then filter for o.OrderId IS NULL. Because OrderId is a non-null primary key on a real order, NULL in the result reliably indicates no match.
- INNER JOIN — matching customer/order pairs
- LEFT JOIN — all customers plus matching orders
| Join | Rows retained |
|---|---|
| INNER JOIN | Only matching customer and order rows |
| LEFT JOIN | Every customer, plus matching orders when present |
Aliases, columns and row counts
c and o are table aliases that make a query shorter. Prefix columns with the alias (c.CustomerId, o.OrderId) so the source is clear. Both tables have CustomerId; writing it without a prefix can be ambiguous.
One customer with three orders produces three result rows. That is not accidental duplication: each output row represents one matching pair. The result grain changed from customer to customer-order. Later lessons examine this carefully.
In this demo Ana (CustomerId 1) has orders 1001, 1002 and 1007, while David (CustomerId 4) has orders 1004 and 1005. Ana appears three times and David twice because each output row represents a separate related order. Ben appears once and Grace twice. A useful check is to count the rows before and after the join and remember what one output row represents. If you need one row per customer, joining detail rows directly will not produce that shape; later aggregation lessons show how to summarize related rows.
A join without its ON condition can pair every customer with every order, producing a Cartesian product. Do not omit the relationship condition. Inspect counts and keys when the result is unexpectedly large.
ON defines how rows relate; WHERE filters the joined result. For example, to show shipped orders while preserving customers with no shipped order, put the status test in 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';Putting WHERE o.Status = N'Shipped' instead removes unmatched customers because their status is NULL. RIGHT JOIN and FULL JOIN also exist, but INNER and LEFT JOIN cover the core beginner patterns.
The placement of a condition matters for an outer join. A predicate in ON participates in deciding which right-side rows match while keeping all left-side customers. A predicate in WHERE is evaluated on the joined output; unmatched rows have NULL order values and fail a test such as o.Status = N'Shipped'. If the goal is to retain every customer while showing only shipped matches, keep the status condition in ON. If the goal is only to list shipped orders, an INNER JOIN followed by WHERE is clearer. Choose based on the rows the result must preserve.
This article focuses on keys and returned rows. For a later look at plans, indexing and performance, see advanced SQL Server join tuning.
Practice
Try: (1) list customers with their orders; (2) retain customers without orders; (3) show shipped orders; (4) find orders above 100.00. The demo includes matching rows for every exercise.
Practice answers
-- 1
SELECT c.FirstName, c.LastName, o.OrderId, o.OrderDate
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.FirstName, o.OrderId, o.Status
FROM dbo.Customers AS c INNER JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId
WHERE o.Status = N'Shipped';
-- 4
SELECT o.OrderId, o.Amount
FROM dbo.Orders AS o
WHERE o.Amount > 100.00;Next, compare INNER JOIN and LEFT JOIN. The Foundations hub lists all lessons.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 9 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFind Blocking in SQL ServerIdentify blocked sessions, blockers, lock waits, and the head blocker so you can troubleshoot SQL Server blocking without killing the wrong session.Open cheat sheet
- RoadmapSQL Server FoundationsLearn SQL Server INNER JOIN and LEFT JOIN with Customers and Orders. Understand keys, aliases, ON conditions and customers without 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.