The orders table has every sale but only a CategoryID number — not the category name. The categories table has the names but no sales data. Riya needs both together. That is exactly what a JOIN is for.

Why joins exist

Well-designed databases split data into separate tables to avoid repetition. Instead of storing "Electronics" thousands of times in the orders table, they store a CategoryID and keep the name once in a separate Category table. JOINs bring these tables back together on demand, using the shared column as the connection.

The four join types

INNER JOIN

Returns only rows that have a match in both tables. Rows with no match on either side are dropped.

SELECT s.Salesperson, c.CategoryName, s.Revenue
FROM SalesFact s
INNER JOIN Category c ON s.CategoryID = c.CategoryID;
💡
Analyst tip: Use table aliases (the single letter after the table name) to keep JOIN queries readable. s.Revenue is much clearer than repeating SalesFact.Revenue every time.

LEFT JOIN

Returns all rows from the left table, plus matching rows from the right. Where there is no match on the right, the right-side columns return NULL. Use this when you want to keep all your primary data and enrich it where possible.

SELECT s.Salesperson, s.Revenue, c.CategoryName
FROM SalesFact s
LEFT JOIN Category c ON s.CategoryID = c.CategoryID;
-- Keeps ALL sales, even if CategoryID has no match

RIGHT JOIN

The mirror of LEFT JOIN — keeps all rows from the right table. Rarely used in practice because you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping table order. Most analysts stick with LEFT JOIN for consistency.

FULL OUTER JOIN

Returns all rows from both tables, with NULLs wherever there is no match. Useful for reconciliation — finding records that exist in one table but not the other.

SELECT s.Salesperson, c.CategoryName
FROM SalesFact s
FULL OUTER JOIN Category c ON s.CategoryID = c.CategoryID;
-- Shows orphaned rows on both sides

Joining more than two tables

SELECT s.Salesperson, c.CategoryName, r.RegionName, s.Revenue
FROM SalesFact s
LEFT JOIN Category c ON s.CategoryID = c.CategoryID
LEFT JOIN Region r ON s.RegionID = r.RegionID;
⚠️
Common mistake: Using INNER JOIN when one of the lookup tables has incomplete data. Any missing match in an INNER JOIN silently removes entire rows from your results. Start with LEFT JOINs and switch to INNER only when you are certain both sides are complete.
🎯 Your Challenge

Write a query joining SalesFact to both Category and Region, returning salesperson name, category name, region name, and revenue. Filter to Electronics category only. Order by revenue descending.

FAQ

A CROSS JOIN returns every combination of rows from both tables (the Cartesian product). If Table A has 100 rows and Table B has 50, CROSS JOIN returns 5,000 rows. Almost never what you want intentionally — if your JOIN returns suspiciously many rows, check you haven't accidentally created one by omitting the ON clause.
Yes. In a LEFT JOIN, the table named first (before LEFT JOIN) is the one whose rows are all kept. The table named after LEFT JOIN is the one that can produce NULLs. Swap the order and the behaviour changes entirely.
`