2.Which Customer Ordered What

INNER JOIN (or just JOIN) keeps only the rows that match on both tables.

SELECT s.SaleDate, p.Name, p.Price
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
WHERE s.SaleDate = '2026-08-29';

Every sale on Aug 29th now shows the product's actual name and price — information that lived only in Products until this JOIN pulled it across.

SELECT SUM(p.Price) AS TotalRevenue
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
WHERE s.SaleDate = '2026-08-29';

This is the exact number Mike asked for all the way back in the SELECT chapter. Sales alone could never produce it — the price only ever existed in Products.

LEFT JOIN keeps every row from the first (left) table, filling in NULL where nothing on the right matches.

SELECT c.Name, o.OrderID
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

An INNER JOIN here would silently drop every customer who's never placed an online order — LEFT JOIN keeps them, with NULL exactly where an order would have been, which is precisely what WHERE ... IS NULL catches.

RIGHT JOIN is the mirror image of LEFT JOIN: it keeps every row from the second (right) table instead.

SELECT c.Name, o.OrderID
FROM Orders o
RIGHT JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL;

This returns the identical result as the LEFT JOIN above — Customers is just written on the right this time. This is exactly why RIGHT JOIN is rare in real code: whatever it can do, a LEFT JOIN with the tables swapped already does.

FULL OUTER JOIN keeps every row from both tables, matched or not, filling in NULL on whichever side has nothing.

SELECT c.Name, o.OrderID
FROM Customers c
FULL OUTER JOIN Orders o ON c.CustomerID = o.CustomerID;

In this particular schema, that happens to return the same rows a LEFT JOIN would — Orders.CustomerID is a NOT NULL foreign key, so an order can never exist without pointing at a real customer. FULL OUTER JOIN's extra behavior only shows something new when the right table can genuinely have rows the left table doesn't match either.

CROSS JOIN combines every row from one table with every row from the other, with no ON clause at all.

SELECT c.Name, p.Name
FROM Customers c
CROSS JOIN Products p;

Eleven customers times six products, paired up — 66 rows. This is also exactly what happens by accident if an INNER JOIN's ON clause is forgotten, which is why an unexpectedly huge result set is a classic sign of a missing join condition.

SELF JOIN joins a table to itself, usually to compare rows within the same table against each other.

SELECT o1.OrderID AS FirstOrder, o2.OrderID AS SecondOrder, o1.CustomerID
FROM Orders o1
JOIN Orders o2 ON o1.CustomerID = o2.CustomerID AND o1.OrderID < o2.OrderID;

Here, Orders is joined to itself to match two different order rows that share the same customer — Priya is the only one who shows up, since she's the only customer who's placed more than one order so far.

SELECT o.OrderID, o.Status, p.Name, oi.Quantity
FROM Orders o
JOIN OrderItems oi ON o.OrderID = oi.OrderID
JOIN Products p ON oi.ProductID = p.ProductID
WHERE o.OrderID = 301;

A JOIN isn't limited to two tables — Orders, OrderItems, and Products all connect in a single query to reconstruct exactly what Priya's order actually contains.