Joins

10.1 Join Types

INNER JOIN       → matching rows only
LEFT  OUTER JOIN → all rows from left + matches (NULLs if none)
RIGHT OUTER JOIN → all rows from right + matches
FULL  OUTER JOIN → all rows from both
CROSS JOIN       → Cartesian product
SELF JOIN        → table joined to itself

10.2 Examples

-- INNER
SELECT o.OrderID, c.FirstName, o.OrderDate
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON c.CustomerID = o.CustomerID;

-- LEFT: customers with or without orders
SELECT c.CustomerID, c.FirstName, o.OrderID
FROM dbo.Customers c
LEFT JOIN dbo.Orders o ON o.CustomerID = c.CustomerID;

-- Anti-join: customers with NO orders
SELECT c.* FROM dbo.Customers c
LEFT JOIN dbo.Orders o ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL;

-- FULL OUTER
SELECT a.ID, b.ID FROM TableA a FULL JOIN TableB b ON a.ID = b.ID;

-- CROSS JOIN: generate combinations
SELECT s.Size, c.Color FROM Sizes s CROSS JOIN Colors c;

-- SELF JOIN: employee and manager
SELECT e.FullName AS Employee, m.FullName AS Manager
FROM dbo.Employees e
LEFT JOIN dbo.Employees m ON m.EmployeeID = e.ManagerID;

-- Multi-table join
SELECT c.FirstName, o.OrderID, p.ProductName, oi.Quantity, oi.LineTotal
FROM dbo.Customers c
JOIN dbo.Orders o       ON o.CustomerID = c.CustomerID
JOIN dbo.OrderItems oi  ON oi.OrderID = o.OrderID
JOIN dbo.Products p     ON p.ProductID = oi.ProductID
WHERE o.OrderDate >= '2025-01-01';

-- Non-equi join
SELECT e.FullName, g.Grade
FROM dbo.Employees e JOIN dbo.SalaryGrades g ON e.Salary BET
…

10.3 ON vs WHERE in Outer Joins

-- Filter in ON: keeps all customers, matches only 2025 orders
SELECT c.CustomerID, o.OrderID FROM dbo.Customers c
LEFT JOIN dbo.Orders o ON o.CustomerID = c.CustomerID AND o.OrderDate >= '2025-01-01';

-- Filter in WHERE: turns the LEFT JOIN into an INNER JOIN!
SELECT c.CustomerID, o.OrderID FROM dbo.Customers c
LEFT JOIN dbo.Orders o ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2025-01-01';

10.4 APPLY Operator

-- CROSS APPLY: like an INNER JOIN to a table-valued expression (e.g., top 3 orders per customer)
SELECT c.CustomerID, c.FirstName, x.OrderID, x.OrderDate
FROM dbo.Customers c
CROSS APPLY (SELECT TOP (3) o.OrderID, o.OrderDate
             FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID
             ORDER BY o.OrderDate DESC) x;

-- OUTER APPLY: like a LEFT JOIN
SELECT c.CustomerID, x.LastOrder
FROM dbo.Customers c
OUTER APPLY (SELECT MAX(OrderDate) AS LastOrder FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID) x;

-- APPLY with a VALUES clause to unpivot
SELECT p.ProductID, v.Attr, v.Val
FROM dbo.Products p
CROSS APPLY (VALUES ('Price', p.UnitPrice), ('Stock', p.Stock)) v(Attr, Val);

10.5 Physical Join Algorithms (Preview of Module 35)

Nested Loops: small outer input with an indexed inner input.

Merge Join: both inputs sorted on the join key.

Hash Join: large unsorted inputs.

Adaptive Join: chosen at runtime (2017+).