Mon 5 October 2026 · 28 queries · run on AdventureWorks2022
Goal: combine any two to four tables correctly, and know from the row count alone whether your join is right.
Read: SQL Joins, SQL Inner Join, SQL Left Join, SQL Right Join, SQL Full Join, SQL Self Join.
Before every join: run SELECT COUNT(*) on both tables and predict the result count. Check it after. A count you did not predict means the join condition is wrong.
SELECT (SELECT COUNT(*) FROM Sales.SalesOrderHeader) AS HeaderRows,
(SELECT COUNT(*) FROM Sales.SalesOrderDetail) AS DetailRows;
What this query does
The habit for every join: count the tables first, then predict the result. There are 31,465 orders (headers) and 121,317 order lines (details). Every line belongs to exactly one order, so joining them should return one row per line: 121,317. If a join returns a number you did not predict, the join condition is wrong.
Result in SSMS: 1 row
#2
1. Orders with their line items. Aliases from the start.
SELECT h.SalesOrderID, h.OrderDate, d.ProductID, d.OrderQty, d.LineTotal
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID;
What this query does
INNER JOIN combines rows from two tables where the ON condition matches. Here each order line is matched to its order through SalesOrderID, so the header columns (OrderDate) appear next to the line columns (ProductID, OrderQty). The order date repeats once for every line of the same order. h and d are table aliases; with two tables, every column should be prefixed so the reader knows where it comes from.
📍 121,317 rows, exactly as predicted: one row per order line.
Result in SSMS: 121,317 rows
#3
2. Products with subcategory name
SELECT p.Name AS Product, s.Name AS Subcategory
FROM Production.Product p
INNER JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID;
-- Fewer rows than Product. Some products have no subcategory.
What this query does
Product only stores a subcategory number; the name lives in ProductSubcategory. The join looks it up. Only 295 of the 504 products come back, because INNER JOIN drops rows with no match: 209 products have a NULL ProductSubcategoryID, and NULL never equals anything.
Result in SSMS: 295 rows
#4
3. Same with LEFT JOIN. Find the ones with NULL subcategory.
SELECT p.Name AS Product, s.Name AS Subcategory
FROM Production.Product p
LEFT JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.ProductSubcategoryID IS NULL;
-- This is the anti-join. You will use it every week of your career.
What this query does
LEFT JOIN keeps every row of the left table (Product). Where there is no matching subcategory, the subcategory columns are filled with NULL. The WHERE then keeps only those unmatched rows: the 209 products with no subcategory. This 'find what is missing' pattern is called an anti-join.
📍 Anti-join: LEFT JOIN, then keep only the rows where the right side is NULL. 295 + 209 = 504.
Result in SSMS: 209 rows
#5
4. Three tables: product, subcategory, category
SELECT p.Name AS Product, s.Name AS Subcategory, c.Name AS Category
FROM Production.Product p
JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
JOIN Production.ProductCategory c ON s.ProductCategoryID = c.ProductCategoryID;
What this query does
Joins chain: product links to subcategory, and subcategory links to category. JOIN on its own means INNER JOIN. The result is still 295 rows, because every subcategory has a category, so the second join neither adds nor removes rows.
Result in SSMS: 295 rows
#6
5. Customer name on orders. Customer links to Person via PersonID.
SELECT h.SalesOrderID, h.OrderDate, pp.FirstName, pp.LastName
FROM Sales.SalesOrderHeader h
JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
JOIN Person.Person pp ON c.PersonID = pp.BusinessEntityID;
What this query does
Orders do not store names. The path is order -> customer (CustomerID) -> person (PersonID = BusinessEntityID). The plan expects fewer rows than the 31,465 orders, because store customers have no PersonID. The result below shows that is not true in this database.
Result in SSMS: 31,465 rows
#7
5. Customer name on orders. Customer links to Person via PersonID. (continued)
-- Swap to LEFT JOIN and count again
SELECT COUNT(*) AS InnerRows FROM Sales.SalesOrderHeader h
JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
JOIN Person.Person pp ON c.PersonID = pp.BusinessEntityID;
SELECT COUNT(*) AS LeftRows FROM Sales.SalesOrderHeader h
LEFT JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
LEFT JOIN Person.Person pp ON c.PersonID = pp.BusinessEntityID;
What this query does
Two queries in one batch give two result grids. Both return 31,465, so here the INNER JOIN lost nothing. 701 customers have no PersonID, but none of them ever placed an order. The 3,806 store orders are linked to the store's contact person instead. Lesson: never assume a join drops or keeps rows; count and check.
📍 Both counts are 31,465. In AdventureWorks2022 every store customer that ordered has a contact person, so INNER loses no rows here.
Result in SSMS: two result sets, InnerRows = 31,465 and LeftRows = 31,465
#8
6. Four tables: order, customer, person, territory, with a filter
SELECT h.SalesOrderID, pp.FirstName, pp.LastName, t.Name AS Territory, h.TotalDue
FROM Sales.SalesOrderHeader h
JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
JOIN Person.Person pp ON c.PersonID = pp.BusinessEntityID
JOIN Sales.SalesTerritory t ON h.TerritoryID = t.TerritoryID
WHERE t.CountryRegionCode = 'GB' AND h.TotalDue > 5000;
What this query does
Four tables joined, then filtered. The joins run first and build wide rows; WHERE then keeps UK orders worth more than 5,000: 107 rows. A WHERE condition can use a column from any joined table (t.CountryRegionCode, h.TotalDue).
Result in SSMS: 107 rows
#9
7. RIGHT JOIN, then rewrite as LEFT with tables swapped. Same result.
SELECT p.Name, s.Name
FROM Production.ProductSubcategory s
RIGHT JOIN Production.Product p ON p.ProductSubcategoryID = s.ProductSubcategoryID;
What this query does
RIGHT JOIN keeps every row of the right table (Product, written second), with NULL subcategory where there is no match: all 504 products.
Result in SSMS: 504 rows
#10
7. RIGHT JOIN, then rewrite as LEFT with tables swapped. Same result. (continued)
SELECT p.Name, s.Name
FROM Production.Product p
LEFT JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID;
What this query does
Swapping the table order and using LEFT JOIN gives exactly the same 504 rows. Any RIGHT JOIN can be rewritten this way, and most teams use only LEFT JOIN so queries read the same way: the main table first.
📍 Same 504 rows. A RIGHT JOIN is a LEFT JOIN with the tables swapped. Most teams only write LEFT JOIN.
Result in SSMS: 504 rows
#11
8. FULL OUTER JOIN. Territories and orders, all of both.
SELECT t.Name, COUNT(h.SalesOrderID) AS Orders
FROM Sales.SalesTerritory t
FULL OUTER JOIN Sales.SalesOrderHeader h ON t.TerritoryID = h.TerritoryID
GROUP BY t.Name;
What this query does
FULL OUTER JOIN keeps unmatched rows from both sides: territories with no orders and orders with no territory. GROUP BY (covered on Day 6) then counts orders per territory. Here every order has a territory and every territory has orders, so the result is the same as an INNER JOIN: 10 rows. COUNT(h.SalesOrderID) is used instead of COUNT(*) so an empty territory would show 0, not 1.
Result in SSMS: 10 rows
#12
9. Self join: employees and their managers via the hierarchy
SELECT e.BusinessEntityID, pe.FirstName + ' ' + pe.LastName AS Employee, e.JobTitle,
pm.FirstName + ' ' + pm.LastName AS Manager
FROM HumanResources.Employee e
JOIN Person.Person pe ON e.BusinessEntityID = pe.BusinessEntityID
LEFT JOIN HumanResources.Employee m ON e.OrganizationNode.GetAncestor(1) = m.OrganizationNode
LEFT JOIN Person.Person pm ON m.BusinessEntityID = pm.BusinessEntityID
ORDER BY e.OrganizationLevel, Employee;
-- The CEO has NULL manager. LEFT JOIN keeps them.
What this query does
A self join uses the same table twice with different aliases: e is the employee, m is the manager. OrganizationNode is a hierarchyid (a position in the org chart, like /6/1/); GetAncestor(1) returns the position one level up, which is the manager's position. Person is joined twice as well, once for each name. LEFT JOINs keep employees with no manager. In AdventureWorks2022 the CEO's OrganizationNode is NULL instead of the root '/', so his 6 direct reports also show NULL. The next query fixes that.
📍 Not only the CEO: 7 people have NULL manager. The CEO's OrganizationNode is NULL, so the 6 vice presidents cannot find him.
Result in SSMS: 290 rows
#13
9. Self join: employees and their managers via the hierarchy (continued)
-- Fix: level-1 employees report to the CEO, whose OrganizationNode is NULL
SELECT e.BusinessEntityID, pe.FirstName + ' ' + pe.LastName AS Employee, e.JobTitle,
pm.FirstName + ' ' + pm.LastName AS Manager
FROM HumanResources.Employee e
JOIN Person.Person pe ON e.BusinessEntityID = pe.BusinessEntityID
LEFT JOIN HumanResources.Employee m
ON m.OrganizationNode = e.OrganizationNode.GetAncestor(1)
OR (e.OrganizationLevel = 1 AND m.OrganizationNode IS NULL)
LEFT JOIN Person.Person pm ON m.BusinessEntityID = pm.BusinessEntityID
ORDER BY e.OrganizationLevel, Employee;
What this query does
The ON clause gets a second rule with OR: a level-1 employee is matched to the one employee whose OrganizationNode is NULL, the CEO. Now the 6 vice presidents show Ken Sánchez as manager, and only Ken himself has NULL. Still 290 rows: one per employee.
📍 Now only the CEO, Ken Sánchez, has NULL manager. A join condition can contain OR.
Result in SSMS: 290 rows
#14
10. Filter in ON vs WHERE. Row counts differ. WHERE turns LEFT into INNER.
SELECT p.Name, d.SalesOrderID
FROM Production.Product p
LEFT JOIN Sales.SalesOrderDetail d ON p.ProductID = d.ProductID AND d.OrderQty > 10;
What this query does
The quantity filter is part of the ON condition, so it only decides which order lines match. Every product is still kept: products with no line of more than 10 units appear once with NULL SalesOrderID. Result: 2,456 rows.
Result in SSMS: 2,456 rows
#15
10. Filter in ON vs WHERE. Row counts differ. WHERE turns LEFT into INNER. (continued)
SELECT p.Name, d.SalesOrderID
FROM Production.Product p
LEFT JOIN Sales.SalesOrderDetail d ON p.ProductID = d.ProductID
WHERE d.OrderQty > 10;
What this query does
Here the filter is in WHERE, which runs after the join. For products with no sales, d.OrderQty is NULL, and NULL > 10 is not true, so those rows are thrown away. The LEFT JOIN now behaves like an INNER JOIN: 2,066 rows, exactly the number of order lines with more than 10 units. Rule: a filter on the right-hand table of a LEFT JOIN belongs in ON, unless you really want the unmatched rows gone.
📍 2,066 rows instead of 2,456: the WHERE removed the 390 NULL rows, turning the LEFT JOIN into an INNER JOIN.
Result in SSMS: 2,066 rows
Stretch
#16
Products never sold. Anti-join.
SELECT p.ProductID, p.Name
FROM Production.Product p
LEFT JOIN Sales.SalesOrderDetail d ON p.ProductID = d.ProductID
WHERE d.SalesOrderDetailID IS NULL;
What this query does
The anti-join again: keep every product, then keep only those with no matching order line. 238 products have never been sold; 266 have. 238 + 266 = 504. Check a column that can never be NULL in a real match, such as the key SalesOrderDetailID.
Result in SSMS: 238 rows
#17
CROSS JOIN. Territories times categories. Small tables only.
SELECT t.Name AS Territory, c.Name AS Category
FROM Sales.SalesTerritory t CROSS JOIN Production.ProductCategory c;
What this query does
CROSS JOIN pairs every row of one table with every row of the other, with no ON condition: 10 territories x 4 categories = 40 rows. Useful to build a grid of every combination (for example, to report zero sales). On big tables it explodes: 31,465 orders x 121,317 lines would be 3.8 billion rows.
Result in SSMS: 40 rows
#18
Non-equality join: which price band is each product in
SELECT p.Name, p.ListPrice,
CASE WHEN p.ListPrice < 100 THEN 'Low' WHEN p.ListPrice < 1000 THEN 'Mid' ELSE 'High' END AS Band
FROM Production.Product p WHERE p.ListPrice > 0;
What this query does
CASE labels each product by checking conditions in order and using the first that is true. A price under 100 is Low, under 1000 is Mid, anything else is High: 90 Low, 128 Mid, 86 High. With a price-band table, the same result could come from a non-equality join such as ON p.ListPrice >= b.MinPrice AND p.ListPrice < b.MaxPrice.
Result in SSMS: 304 rows
#19
Forget the ON clause. Read the error.
SELECT * FROM Sales.SalesTerritory t JOIN Production.ProductCategory c;
What this query does
JOIN (INNER JOIN) must be followed by ON. Without it SQL Server reaches the semicolon while still expecting ON, so it reports error 102: incorrect syntax near ';'. The message points to where the parser gave up, not the missing word, so look just before the position it names.
Result in SSMS: Error (expected): Incorrect syntax near ';'.
#20
Forget the ON clause. Read the error. (continued)
-- ON 1 = 1 is always true, so it becomes a cross join
SELECT t.Name AS Territory, c.Name AS Category
FROM Sales.SalesTerritory t JOIN Production.ProductCategory c ON 1 = 1;
What this query does
1 = 1 is true for every pair of rows, so every territory matches every category: the same 40 rows as CROSS JOIN. A join condition that is accidentally always true (or matches on the wrong column) causes the same row explosion, which is why you predict the row count first.
📍 ON 1 = 1 matches every pair, so this gives the same 40 rows as CROSS JOIN.
Result in SSMS: 40 rows
#21
Duplication from a join. A product with 100 sales appears 100 times.
SELECT p.Name, COUNT(*) AS Rows
FROM Production.Product p
JOIN Sales.SalesOrderDetail d ON p.ProductID = d.ProductID
GROUP BY p.Name ORDER BY Rows DESC;
What this query does
Joining a product to its order lines repeats the product once per line. The Water Bottle - 30 oz. appears on 4,688 lines, so after the join it is 4,688 rows. This is why SUM(p.ListPrice) after such a join would be wildly inflated: always ask 'one row per what?' after a join.
Result in SSMS: 266 rows
#22
Ambiguous column. No prefix on ProductID after joining two tables that have it.
SELECT ProductID FROM Production.Product p JOIN Sales.SalesOrderDetail d ON p.ProductID = d.ProductID;
What this query does
Error 209: ambiguous column name. Both tables have a ProductID column, so SQL Server does not know which one you mean, even though here they hold the same value. The fix is a prefix: p.ProductID or d.ProductID. Prefixing every column in a join avoids this error entirely.
Result in SSMS: Error (expected): Ambiguous column name 'ProductID'.
#23
Salesperson name on orders. Employee to Person again.
SELECT h.SalesOrderID, pp.FirstName + ' ' + pp.LastName AS SalesPerson, h.TotalDue
FROM Sales.SalesOrderHeader h
LEFT JOIN Person.Person pp ON h.SalesPersonID = pp.BusinessEntityID;
-- Most orders have NULL salesperson. Online orders have none.
What this query does
SalesPersonID is the salesperson's BusinessEntityID, so it can join straight to Person.Person. LEFT JOIN keeps all 31,465 orders. 27,659 of them have no salesperson, exactly the number of online orders from Day 4: a person sells only to stores. An INNER JOIN would have silently dropped 88% of the orders.
📍 27,659 orders have NULL salesperson, exactly the number of online orders.
Result in SSMS: 31,465 rows
Self-test
#24
Q1. Every territory and how many orders it has, including zero
SELECT t.Name AS Territory, COUNT(h.SalesOrderID) AS Orders
FROM Sales.SalesTerritory t
LEFT JOIN Sales.SalesOrderHeader h ON t.TerritoryID = h.TerritoryID
GROUP BY t.Name
ORDER BY Orders DESC;
What this query does
Start from the table that must be complete (territories) and LEFT JOIN the orders, so a territory with no orders would still appear. COUNT(h.SalesOrderID) counts only real matches, giving 0 rather than 1 for an empty territory. Australia leads with 6,843 orders; Central has the fewest, 385.
Result in SSMS: 10 rows
#25
Q2. The line items of order 43659 with product names and line totals
SELECT d.SalesOrderDetailID, p.Name AS Product, d.OrderQty, d.UnitPrice, d.LineTotal
FROM Sales.SalesOrderDetail d
JOIN Production.Product p ON d.ProductID = p.ProductID
WHERE d.SalesOrderID = 43659;
What this query does
Filter the order lines to one order, then join Product for the names. Order 43659 has 12 lines totalling 20,565.62. The order's TotalDue is 23,153.23 because it also includes tax and freight.
Result in SSMS: 12 rows
#26
Q3. Customers who ordered anything in the Bikes category
SELECT DISTINCT h.CustomerID
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
JOIN Production.Product p ON d.ProductID = p.ProductID
JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
JOIN Production.ProductCategory c ON s.ProductCategoryID = c.ProductCategoryID
WHERE c.Name = 'Bikes';
What this query does
Five tables chained from order to category, filtered to Bikes. One customer can buy many bikes, so the joins repeat customers; DISTINCT reduces the result to one row per customer: 9,719 customers.
Result in SSMS: 9,719 rows
#27
Q4. Employees whose job title contains 'Manager', with their manager's name
SELECT pe.FirstName + ' ' + pe.LastName AS Employee, e.JobTitle,
pm.FirstName + ' ' + pm.LastName AS Manager
FROM HumanResources.Employee e
JOIN Person.Person pe ON e.BusinessEntityID = pe.BusinessEntityID
LEFT JOIN HumanResources.Employee m
ON m.OrganizationNode = e.OrganizationNode.GetAncestor(1)
OR (e.OrganizationLevel = 1 AND m.OrganizationNode IS NULL)
LEFT JOIN Person.Person pm ON m.BusinessEntityID = pm.BusinessEntityID
WHERE e.JobTitle LIKE '%Manager%';
What this query does
The fixed self join from drill 9 with a LIKE filter on the job title: 17 managers. Because of the fix, David Bradley (Marketing Manager), who reports straight to the CEO, correctly shows Ken Sánchez instead of NULL.
Result in SSMS: 17 rows
#28
Q5. Orders whose customer is a store, not a person
SELECT h.SalesOrderID, h.OrderDate, st.Name AS Store, h.TotalDue
FROM Sales.SalesOrderHeader h
JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
JOIN Sales.Store st ON c.StoreID = st.BusinessEntityID
WHERE c.StoreID IS NOT NULL;
What this query does
A store customer has a StoreID, which links to Sales.Store for the store name. The result is 3,806 orders, exactly the store (non-online) orders counted on Day 4. The WHERE is technically redundant, because the INNER JOIN to Store already drops customers with no StoreID, but it states the intent clearly.
Result in SSMS: 3,806 rows
Join cheat sheet:INNER JOIN keeps only matches. LEFT JOIN keeps every row of the first table, with NULLs where nothing matched. LEFT JOIN ... WHERE right.key IS NULL is the anti-join (rows with no match). A filter on the right-hand table belongs in ON; in WHERE it turns a LEFT JOIN into an INNER JOIN.