Day 8: Advanced JOIN techniques

A. Lesson overview

Activity breakdown

Business scenario. Operations wants a stock coverage report showing every product in every warehouse — including combinations with no stock record at all. Finance has noticed that a colleague’s “sales versus payments” report shows payments of almost £300,000 when the true figure is about £121,000. HR wants a department list that includes departments with no staff. Each problem is a join problem: missing rows, extra rows, or rows in the wrong place.

B. Theory explained clearly

Part 1 — How joins work

B1. A join is a filtered pairing

Logically, a join first pairs every row of the left table with every row of the right table (a Cartesian product), then keeps the pairs for which the ON condition is TRUE. Outer joins then add back unmatched rows. SQL Server never actually builds the full product — its join algorithms are far more efficient — but this logical model always predicts the correct result.

B2. Cardinality decides the row count

Joining to a parent never changes the number of child rows (if every child has a parent). Joining to a child multiplies parent rows. Ask “one row per what?” after every join.

B3. NULL never matches

ON s.StoreID = o.StoreID is UNKNOWN when o.StoreID is NULL, so the pair is rejected. An inner join from orders to stores returns only the 158 store orders; the 218 online orders vanish. If you need all orders, use a LEFT JOIN to the store.

The same applies when both sides are NULL: two NULL emails do not match each other.

Part 2 — Outer joins

B4. The three outer joins

A RIGHT JOIN B returns the same rows as B LEFT JOIN A. Most teams write only LEFT joins, putting the table whose rows must all be kept first; it makes chains of joins easier to read.

B5. Counting after an outer join

SELECT  d.DepartmentName,
        COUNT(*)            AS CountStar,       -- counts the NULL-filled row too
        COUNT(e.EmployeeID) AS Employees        -- counts only real matches
FROM    HR.Department AS d
LEFT JOIN HR.Employee AS e ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

Marketing has no employees: COUNT(*) returns 1 (the single NULL-padded row), COUNT(e.EmployeeID) returns 0. Count a non-nullable column from the outer side, normally its primary key.

B6. ON versus WHERE

For inner joins, a condition gives the same result in ON or WHERE. For outer joins it does not:

Rule of thumb for a LEFT JOIN:

• Conditions on the right table that describe which rows may match → ON.
• Conditions on the left table that decide which left rows appear → WHERE.
• WHERE right.Key IS NULL is the deliberate exception: the anti-join pattern.

B7. Chains of joins

Joins are evaluated left to right (logically). A trap: an INNER JOIN that follows a LEFT JOIN and refers to the left-joined table removes the NULL rows again.

FROM Sales.Customer AS c
LEFT JOIN Sales.SalesOrder AS o ON o.CustomerID = c.CustomerID
JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID     -- customers without orders disappear

Fix it by making the later join a LEFT JOIN too, or by joining orders to lines first (in a CTE or with parentheses) and left-joining that result to customers.

Part 3 — CROSS, self- and non-equi joins

B8. CROSS JOIN

CROSS JOIN returns every combination: 29 active products × 2 warehouses = 58 rows. Uses:

• Building a scaffold of all expected combinations (product × warehouse, store × month), then LEFT JOIN the facts to it to expose gaps — “show zero, not nothing”.
• Pairing every row with a one-row result (e.g. a parameters CTE).

Accidental Cartesian products — a forgotten ON condition in older comma-separated syntax (FROM a, b) — are a classic source of enormous wrong results. Write explicit JOIN … ON.

B9. Self-joins

A self-join joins a table to itself with two aliases. The employee–manager relationship is the classic case:

SELECT  e.EmployeeID, e.FirstName AS Employee, m.FirstName AS Manager
FROM    HR.Employee AS e
LEFT JOIN HR.Employee AS m ON m.EmployeeID = e.ManagerID;    -- LEFT keeps the Managing Director

Self-joins are also used to pair rows within a group, such as two products on the same order (the stretch challenge).

B10. Non-equi joins

The ON condition can be any predicate, not just equality: ranges (BETWEEN), inequalities (<, >) or combinations. Typical uses:

• Mapping values to bands defined in a table (price bands, tax brackets, effective-date ranges).
• Pairing rows that are “close” to each other (hire dates within 90 days).
• Avoiding duplicate pairs in self-joins: a.ID < b.ID returns each unordered pair once, while a.ID <> b.ID returns it twice.

Non-equi joins can produce many rows; check the grain carefully.

Part 4 — Fan-out with two child tables

An order has lines and payments — two independent child tables. Joining both to the order pairs every line with every payment of that order:

Four rows. SUM(Amount) counts each payment twice (1,714.00 instead of 857.00), and SUM of the line values also doubles. Across the whole database, joining orders → lines → payments returns 749 rows and payments of 291,761.47 instead of 120,861.55.

Fix: aggregate each child to the parent’s grain first, then join the aggregates:

WITH LineTotals AS (SELECT OrderID, SUM(…) AS OrderNet  FROM Sales.SalesOrderLine GROUP BY OrderID),
     PayTotals  AS (SELECT OrderID, SUM(Amount) AS Paid FROM Sales.Payment       GROUP BY OrderID)
SELECT …
FROM   Sales.SalesOrder AS o
LEFT JOIN LineTotals AS lt ON lt.OrderID = o.OrderID
LEFT JOIN PayTotals  AS pt ON pt.OrderID = o.OrderID;

Now each CTE has one row per order and the join cannot multiply anything. Adding DISTINCT to “fix” fan-out is a mistake: it hides the symptom and can remove legitimate duplicate values (two payments of the same amount).

C. Dataset and starting state

Preparation. None if RetailLab is unchanged. Otherwise run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive) or scripts 00 → 03.

Validation. Expected: 6, 49, 58, 395.

USE RetailLab;
GO

SELECT  (SELECT COUNT(*) FROM HR.Department)        AS Departments,
        (SELECT COUNT(*) FROM Inventory.Stock)      AS StockRows,
        (SELECT COUNT(*) FROM Inventory.Product AS p CROSS JOIN Inventory.Warehouse AS w
         WHERE p.DiscontinuedDate IS NULL)          AS ProductWarehouseCombos,
        (SELECT COUNT(*) FROM Sales.Payment)        AS Payments;

D. Worked examples

Worked example 1 — Inner join drops NULL keys

SELECT  COUNT(*) AS OrdersWithStore
FROM    Sales.SalesOrder AS o
JOIN    Sales.Store      AS s ON s.StoreID = o.StoreID;

SELECT  COUNT(*) AS AllOrders
FROM    Sales.SalesOrder AS o
LEFT JOIN Sales.Store    AS s ON s.StoreID = o.StoreID;

Expected result: 158 and 376. The inner join silently removed 218 online orders because NULL = 1 is UNKNOWN. Neither query is wrong; they answer different questions.

Worked example 2 — Departments including the empty one

SELECT  d.DepartmentID,
        d.DepartmentName,
        COUNT(*)                                 AS CountStar_Wrong,
        COUNT(e.EmployeeID)                      AS CurrentEmployees,
        CAST(AVG(e.Salary) AS decimal(10,2))     AS AvgSalary
FROM    HR.Department AS d
LEFT JOIN HR.Employee AS e
       ON e.DepartmentID = d.DepartmentID
      AND e.TerminationDate IS NULL              -- right-table condition in ON
GROUP BY d.DepartmentID, d.DepartmentName
ORDER BY d.DepartmentID;

Expected result

Move e.TerminationDate IS NULL into a WHERE clause and Marketing stays: its padded row has a NULL termination date, and NULL IS NULL is TRUE. Now try WHERE e.Salary > 0: Marketing disappears. Conditions in WHERE on outer-joined columns must be reasoned through carefully.

Worked example 3 — The same report with RIGHT JOIN

SELECT  d.DepartmentName, COUNT(e.EmployeeID) AS CurrentEmployees
FROM    HR.Employee   AS e
RIGHT JOIN HR.Department AS d
       ON  d.DepartmentID = e.DepartmentID
       AND e.TerminationDate IS NULL
GROUP BY d.DepartmentName
ORDER BY d.DepartmentName;

Expected result: the same 6 departments and counts as example 2. RIGHT JOIN keeps every department. It works, but most people find the LEFT JOIN version easier to read.

Worked example 4 — Reconcile staging to customers (FULL OUTER JOIN)

SELECT  CASE
            WHEN i.ImportRowID IS NOT NULL AND c.CustomerID IS NOT NULL THEN 'In both'
            WHEN c.CustomerID IS NULL                                    THEN 'Only in import'
            ELSE                                                              'Only in customers'
        END        AS MatchStatus,
        COUNT(*)   AS Rows
FROM    Staging.CustomerImport AS i
FULL OUTER JOIN Sales.Customer AS c
        ON c.Email = TRIM(i.Email)          -- case-insensitive collation: no LOWER needed
GROUP BY CASE
            WHEN i.ImportRowID IS NOT NULL AND c.CustomerID IS NOT NULL THEN 'In both'
            WHEN c.CustomerID IS NULL                                    THEN 'Only in import'
            ELSE                                                              'Only in customers'
        END
ORDER BY MatchStatus;

Expected result

What to notice: the three matches are import rows 1, 2 and 11 (Alice Morgan, Ben Carter and Mohammed Rahman). Row 14, Emma Wilson, does not match customer 5 because the customer record has no email — NULL never matches. A reconciliation that only uses one key misses that, which is why Day 30’s load process also compares names. 52 rows = 3 + 12 + 37.

Worked example 5 — Employees with their managers (self-join)

SELECT  e.EmployeeID,
        CONCAT_WS(N' ', e.FirstName, e.LastName)                    AS Employee,
        e.JobTitle,
        COALESCE(CONCAT_WS(N' ', m.FirstName, m.LastName), N'—')    AS Manager
FROM    HR.Employee AS e
LEFT JOIN HR.Employee AS m ON m.EmployeeID = e.ManagerID
ORDER BY e.EmployeeID;

Expected result: 16 rows; Eleanor Hughes’s Manager is blank — not — (see the note below). With an inner join she would disappear (15 rows).

Careful: CONCAT_WS returns an empty string, not NULL, when all its arguments are NULL — so for Eleanor, COALESCE receives '' and returns '', not —. To show the dash, test the key instead: CASE WHEN m.EmployeeID IS NULL THEN N'—' ELSE CONCAT_WS(…) END. Run both versions and compare row 1.

Worked example 6 — Price bands from a lookup (non-equi join)

WITH PriceBand AS
(
    SELECT * FROM (VALUES ('Budget',      0.00,   49.99),
                          ('Mid-range',  50.00,  149.99),
                          ('Premium',   150.00, 99999.99)) AS b (BandName, LowPrice, HighPrice)
)
SELECT  b.BandName,
        COUNT(*)              AS Products,
        MIN(p.ListPrice)      AS Cheapest,
        MAX(p.ListPrice)      AS Dearest
FROM    Inventory.Product AS p
JOIN    PriceBand         AS b ON p.ListPrice BETWEEN b.LowPrice AND b.HighPrice
GROUP BY b.BandName
ORDER BY MIN(b.LowPrice);

Expected result: Budget 8 (6.50–49.00), Mid-range 10 (59.00–139.00), Premium 12 (159.00–599.00) — the same as Day 1’s CASE, but the band boundaries now live in data that a business user could maintain. Overlapping or gapped bands would duplicate or drop products; Day 21 shows how to validate a lookup table.

Worked example 7 — Missing stock records (CROSS JOIN scaffold)

SELECT  w.WarehouseName, p.ProductCode, p.ProductName
FROM    Inventory.Product   AS p
CROSS JOIN Inventory.Warehouse AS w
LEFT JOIN Inventory.Stock   AS s
       ON  s.ProductID   = p.ProductID
       AND s.WarehouseID = w.WarehouseID
WHERE   p.DiscontinuedDate IS NULL
  AND   s.ProductID IS NULL                       -- no stock row for this combination
ORDER BY w.WarehouseName, p.ProductCode;

Expected result: 9 rows, all in Scotland Hub: BPK-002, BSL-001, COK-003, FTW-001, JKT-003, LGT-001, NAV-001, SLP-003 and TNT-003. A missing row is different from a row with 0 units — the Scotland Hub does not stock these products at all.

Worked example 8 — Filter placement in a LEFT JOIN

-- A: condition in ON — every customer, with their December orders (if any)
SELECT  COUNT(*) AS RowsA, COUNT(DISTINCT c.CustomerID) AS CustomersA
FROM    Sales.Customer AS c
LEFT JOIN Sales.SalesOrder AS o
       ON  o.CustomerID = c.CustomerID
       AND o.OrderDate >= '2025-12-01';

-- B: condition in WHERE — only customers who HAVE a December order
SELECT  COUNT(*) AS RowsB, COUNT(DISTINCT c.CustomerID) AS CustomersB
FROM    Sales.Customer AS c
LEFT JOIN Sales.SalesOrder AS o
       ON  o.CustomerID = c.CustomerID
WHERE   o.OrderDate >= '2025-12-01';

Expected result: A returns 47 rows for 40 customers (27 December orders + 20 customers with none). B returns 27 rows for 20 customers — exactly an inner join.

Worked example 9 — Fan-out, demonstrated and fixed

-- Wrong: lines × payments
SELECT  CAST(SUM(p.Amount) AS decimal(12,2)) AS PaymentsReported
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
JOIN    Sales.Payment        AS p ON p.OrderID = o.OrderID;

-- One order in detail
SELECT  o.OrderID, l.ProductID, p.PaymentID, p.Amount
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
JOIN    Sales.Payment        AS p ON p.OrderID = o.OrderID
WHERE   o.OrderID = 1034
ORDER BY l.OrderLineID, p.PaymentID;

-- Right: aggregate each child first
WITH PayTotals AS
(
    SELECT OrderID, SUM(Amount) AS Paid FROM Sales.Payment GROUP BY OrderID
)
SELECT  CAST(SUM(pt.Paid) AS decimal(12,2)) AS PaymentsCorrect
FROM    Sales.SalesOrder AS o
JOIN    PayTotals        AS pt ON pt.OrderID = o.OrderID;

Expected result: 291761.47; then 4 rows for order 1034 (each of the two payments repeated for each of the two lines); then 120861.55, which matches SELECT SUM(Amount) FROM Sales.Payment.

E. Guided practical lab — Stock coverage report

Operations wants one row for every active product in every warehouse: stock on hand, reorder level, 2025 units sold (company-wide), and a status.

Step 1 — Build the scaffold

1. Do: Cross join active products and warehouses.
2. Run:

   USE RetailLab;
   GO
   
   SELECT  w.WarehouseID, w.WarehouseName, p.ProductID, p.ProductCode
   FROM    Inventory.Product   AS p
   CROSS JOIN Inventory.Warehouse AS w
   WHERE   p.DiscontinuedDate IS NULL
   ORDER BY w.WarehouseID, p.ProductCode;

3. Observe: 58 rows.
4. Why it matters: The scaffold defines the report’s grain: one row per product per warehouse, whether or not data exists.
5. Confirm: 58 = 29 × 2.

Step 2 — Attach stock with a LEFT JOIN

1. Do: Left-join Inventory.Stock on both key columns.
2. Run:

   SELECT  w.WarehouseID, p.ProductCode, s.QuantityOnHand, s.ReorderLevel
   FROM    Inventory.Product   AS p
   CROSS JOIN Inventory.Warehouse AS w
   LEFT JOIN Inventory.Stock   AS s
          ON  s.ProductID   = p.ProductID
          AND s.WarehouseID = w.WarehouseID
   WHERE   p.DiscontinuedDate IS NULL
   ORDER BY w.WarehouseID, p.ProductCode;

3. Observe: still 58 rows; 9 have NULL quantity.
4. Why it matters: Joining on only ProductID would match each product to both warehouses’ stock rows, doubling the result. A composite key needs every key column in ON.
5. Confirm: 58 rows; SUM(QuantityOnHand) over the result is 1142 (the stock table’s total).

Step 3 — Add 2025 units sold without fan-out

1. Do: Aggregate sales to one row per product in a CTE, then left-join it.
2. Run:

   WITH Units2025 AS
   (
       SELECT  l.ProductID, SUM(l.Quantity) AS UnitsSold2025
       FROM    Sales.SalesOrder     AS o
       JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
       WHERE   o.OrderStatus <> 'Cancelled'
         AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
       GROUP BY l.ProductID
   )
   SELECT  w.WarehouseName, p.ProductCode, p.ProductName,
           s.QuantityOnHand, s.ReorderLevel,
           COALESCE(u.UnitsSold2025, 0) AS UnitsSold2025
   FROM    Inventory.Product   AS p
   CROSS JOIN Inventory.Warehouse AS w
   LEFT JOIN Inventory.Stock   AS s ON s.ProductID = p.ProductID AND s.WarehouseID = w.WarehouseID
   LEFT JOIN Units2025         AS u ON u.ProductID = p.ProductID
   WHERE   p.DiscontinuedDate IS NULL
   ORDER BY w.WarehouseName, p.ProductCode;

3. Observe: still 58 rows. The Satellite Messenger (NAV-004) shows 0 units sold.
4. Why it matters: Joining raw order lines here would multiply each scaffold row by the number of lines sold. Pre-aggregating keeps the grain.
5. Confirm: 58 rows.

Step 4 — Status and an action list

1. Do: Add a status column, then wrap the query in a CTE and summarise.

2. Run:

   WITH Units2025 AS
   (
       SELECT  l.ProductID, SUM(l.Quantity) AS UnitsSold2025
       FROM    Sales.SalesOrder     AS o
       JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
       WHERE   o.OrderStatus <> 'Cancelled'
         AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
       GROUP BY l.ProductID
   ),
   Coverage AS
   (
       SELECT  w.WarehouseName, p.ProductCode, p.ProductName,
               s.QuantityOnHand, s.ReorderLevel,
               COALESCE(u.UnitsSold2025, 0) AS UnitsSold2025,
               CASE
                   WHEN s.ProductID IS NULL                  THEN 'Not stocked here'
                   WHEN s.QuantityOnHand = 0                 THEN 'Out of stock'
                   WHEN s.QuantityOnHand <= s.ReorderLevel   THEN 'Reorder'
                   ELSE                                           'OK'
               END AS StockStatus
       FROM    Inventory.Product   AS p
       CROSS JOIN Inventory.Warehouse AS w
       LEFT JOIN Inventory.Stock   AS s ON s.ProductID = p.ProductID AND s.WarehouseID = w.WarehouseID
       LEFT JOIN Units2025         AS u ON u.ProductID = p.ProductID
       WHERE   p.DiscontinuedDate IS NULL
   )
   SELECT  WarehouseName, StockStatus, COUNT(*) AS Products
   FROM    Coverage
   GROUP BY WarehouseName, StockStatus
   ORDER BY WarehouseName, StockStatus;

3. Observe:

   WarehouseName	StockStatus	Products
   Midlands Distribution Centre	OK	22
   Midlands Distribution Centre	Reorder	7
   Scotland Hub	Not stocked here	9
   Scotland Hub	OK	14
   Scotland Hub	Out of stock	2
   Scotland Hub	Reorder	4

4. Why it matters: Testing s.ProductID IS NULL first distinguishes “no record” from “zero units” — the CASE order matters.

5. Confirm: the counts add up to 58.

Step 5 — The action list

1. Do: Replace the final SELECT to list products that are out of stock or need reordering, most in-demand first.
2. Run:

   SELECT  StockStatus, WarehouseName, ProductCode, ProductName,
           QuantityOnHand, ReorderLevel, UnitsSold2025
   FROM    Coverage
   WHERE   StockStatus IN ('Out of stock', 'Reorder')
   ORDER BY CASE StockStatus WHEN 'Out of stock' THEN 1 ELSE 2 END,
            UnitsSold2025 DESC, ProductCode, WarehouseName;

3. Observe: 13 rows. The first two are Out of stock at Scotland Hub: TNT-002 Summit 4-Person Tent (26 sold in 2025) and NAV-002 Handheld GPS Unit (14 sold). The first Reorder rows are BPK-003 Kids Daypack at both warehouses (22 sold).
4. Why it matters: A useful operational list combines four tables at two different grains — without a single duplicated row.
5. Confirm: 13 rows.

F. Independent practice

Task 1 — Headcount by department, including empty ones

Return every department with CurrentEmployees, Leavers (employees with a termination date) and TotalSalaryCurrent (0 when there are none). Sort by DepartmentName.

Success criteria: 6 rows. Sales: 7 current, 1 leaver, 298000.00. Marketing: 0, 0, 0.00.

Task 2 — December order register

For orders placed in December 2025, return OrderID, OrderDate, CustomerName, Location (the store name, or Online) and ServedBy (salesperson’s full name, or — for online orders). Sort by date, then order ID.

Success criteria: 27 rows; 18 Online, 5 Manchester Northern Quarter, 3 Edinburgh Old Town, 1 London Covent Garden. Order 1352 was served by Liam O’Connor in Edinburgh.

Task 3 — Hires close together (self-join + non-equi)

List pairs of employees where the second was hired after the first and within 90 days of them. Return both IDs, names, hire dates and DaysApart. Each pair must appear once. Sort by the first hire date.

Success criteria: 4 pairs — Fiona Campbell → Tom Baxter (56 days), Liam O’Connor → Gareth Price (84), James Reid → Hannah Scott (84), Marek Nowak → Chen Wei (49).

Task 4 — What do customers still owe? (fan-out, done right)

For 2025 non-cancelled orders, return per customer: Orders, AmountDue (each order’s net rounded to 2 decimal places, plus shipping), AmountPaid and Outstanding. Show only customers with something outstanding, largest first.

Success criteria: 4 rows — customer 1 owes 830.69, customer 13 662.15, customer 17 129.00 and customer 15 19.94. Across all 34 customers with 2025 orders, AmountDue totals 63835.97 and AmountPaid 62194.19.

Stretch challenge — Frequently bought together

Using a self-join of Sales.SalesOrderLine on OrderID, count how many non-cancelled orders contain each pair of different products. Return the pairs bought together in at least 5 orders, with both product codes and names. Each pair once.

Success criteria: 3 pairs, each in 5 orders: TNT-001 + TNT-003; JKT-002 + BSL-001; JKT-004 + FTW-003.

G. Solutions and explanations

SELECT  d.DepartmentName,
        COUNT(CASE WHEN e.TerminationDate IS NULL AND e.EmployeeID IS NOT NULL THEN 1 END) AS CurrentEmployees,
        COUNT(e.TerminationDate)                                                          AS Leavers,
        COALESCE(SUM(CASE WHEN e.TerminationDate IS NULL THEN e.Salary END), 0)           AS TotalSalaryCurrent
FROM    HR.Department AS d
LEFT JOIN HR.Employee AS e ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName
ORDER BY d.DepartmentName;

SELECT  o.OrderID,
        o.OrderDate,
        CONCAT_WS(N' ', c.FirstName, c.LastName)                       AS CustomerName,
        COALESCE(s.StoreName, N'Online')                               AS Location,
        CASE WHEN e.EmployeeID IS NULL THEN N'—'
             ELSE CONCAT_WS(N' ', e.FirstName, e.LastName) END         AS ServedBy
FROM    Sales.SalesOrder AS o
JOIN    Sales.Customer   AS c ON c.CustomerID = o.CustomerID
LEFT JOIN Sales.Store    AS s ON s.StoreID    = o.StoreID
LEFT JOIN HR.Employee    AS e ON e.EmployeeID = o.EmployeeID
WHERE   o.OrderDate >= '2025-12-01' AND o.OrderDate < '2026-01-01'
ORDER BY o.OrderDate, o.OrderID;

SELECT  a.EmployeeID                              AS FirstID,
        CONCAT_WS(N' ', a.FirstName, a.LastName)  AS FirstHire,
        a.HireDate                                AS FirstHireDate,
        b.EmployeeID                              AS SecondID,
        CONCAT_WS(N' ', b.FirstName, b.LastName)  AS SecondHire,
        b.HireDate                                AS SecondHireDate,
        DATEDIFF(DAY, a.HireDate, b.HireDate)     AS DaysApart
FROM    HR.Employee AS a
JOIN    HR.Employee AS b
        ON  b.HireDate > a.HireDate
        AND b.HireDate <= DATEADD(DAY, 90, a.HireDate)
ORDER BY a.HireDate, b.HireDate;

WITH OrderDue AS
(
    SELECT  o.OrderID, o.CustomerID,
            ROUND(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)), 2) + o.ShippingCost AS AmountDue
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY o.OrderID, o.CustomerID, o.ShippingCost
),
OrderPaid AS
(
    SELECT OrderID, SUM(Amount) AS AmountPaid
    FROM   Sales.Payment
    GROUP BY OrderID
),
CustomerBalance AS
(
    SELECT  d.CustomerID,
            COUNT(*)                          AS Orders,
            SUM(d.AmountDue)                  AS AmountDue,
            SUM(COALESCE(p.AmountPaid, 0))    AS AmountPaid
    FROM    OrderDue  AS d
    LEFT JOIN OrderPaid AS p ON p.OrderID = d.OrderID
    GROUP BY d.CustomerID
)
SELECT  CustomerID, Orders,
        CAST(AmountDue  AS decimal(12,2))              AS AmountDue,
        CAST(AmountPaid AS decimal(12,2))              AS AmountPaid,
        CAST(AmountDue - AmountPaid AS decimal(12,2))  AS Outstanding
FROM    CustomerBalance
WHERE   AmountDue - AmountPaid > 0
ORDER BY Outstanding DESC, CustomerID;

WITH Pairs AS
(
    SELECT  a.ProductID AS ProductA,
            b.ProductID AS ProductB,
            COUNT(*)    AS OrdersTogether
    FROM    Sales.SalesOrderLine AS a
    JOIN    Sales.SalesOrderLine AS b
            ON  b.OrderID   = a.OrderID
            AND a.ProductID < b.ProductID          -- each unordered pair once
    JOIN    Sales.SalesOrder     AS o ON o.OrderID = a.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
    GROUP BY a.ProductID, b.ProductID
)
SELECT  pa.ProductCode AS CodeA, pa.ProductName AS ProductA,
        pb.ProductCode AS CodeB, pb.ProductName AS ProductB,
        x.OrdersTogether
FROM    Pairs             AS x
JOIN    Inventory.Product AS pa ON pa.ProductID = x.ProductA
JOIN    Inventory.Product AS pb ON pb.ProductID = x.ProductB
WHERE   x.OrdersTogether >= 5
ORDER BY x.OrdersTogether DESC, pa.ProductCode, pb.ProductCode;

H. Knowledge check

1. Understanding. An inner join from Sales.SalesOrder (376 rows) to Sales.Customer returns how many rows, and why? What about Sales.Customer inner-joined to Sales.SalesOrder?

2. Debugging. A department report uses COUNT(*) after LEFT JOIN HR.Employee and shows Marketing with 1 employee. Explain and fix.

3. Query interpretation. What is the difference between these two queries’ results?

   -- A
   SELECT c.CustomerID, o.OrderID FROM Sales.Customer AS c
   LEFT JOIN Sales.SalesOrder AS o ON o.CustomerID = c.CustomerID AND o.SalesChannel = 'Store';
   -- B
   SELECT c.CustomerID, o.OrderID FROM Sales.Customer AS c
   LEFT JOIN Sales.SalesOrder AS o ON o.CustomerID = c.CustomerID WHERE o.SalesChannel = 'Store';

4. Debugging. A query joins orders to lines and to payments and reports total payments of £291,761.47. What happened and how do you fix it?

5. Understanding. Why does Staging.CustomerImport row 14 (Emma Wilson) not match customer 5 on email?

I. Troubleshooting and cleanup

Cleanup. Day 8 is read-only; nothing to clean up. Keep RetailLab unchanged for Day 9.

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 returned 158 and 376, and you can explain the difference.
• Worked example 8 returned 47/40 (filter in ON) and 27/20 (filter in WHERE).
• Worked example 9 showed 291761.47 before and 120861.55 after the fan-out fix.
• Your stock coverage report has 58 rows and a 13-row action list.
• Tasks 1–4 match the success criteria (6, 27, 4 and 4 rows).

Recap

• Predict row counts from cardinality: parents don’t multiply children; children multiply parents.
• NULL keys never match; inner joins drop them silently.
• LEFT keeps the left table, RIGHT the right, FULL both; count matches with COUNT(right.PrimaryKey).
• For outer joins, right-table conditions go in ON; WHERE on right-table columns usually turns the join into an inner join.
• CROSS JOIN builds scaffolds of expected combinations; outer-join facts to them to show gaps.
• Self-joins use two aliases; non-equi joins match on ranges or inequalities; a.ID < b.ID avoids duplicate pairs.
• Two child tables joined to one parent fan out. Aggregate each child to the parent grain first.

Next: Day 9 — Set operations: UNION, UNION ALL, INTERSECT and EXCEPT, including how they treat NULLs and duplicates.

References (Microsoft Learn)

• FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
• Joins (SQL Server)
• Using outer joins
• COUNT (Transact-SQL)
• CONCAT_WS (Transact-SQL)
• DATEADD (Transact-SQL)