SQL Server JOIN Types Explained: INNER, LEFT, RIGHT, FULL, CROSS, SELF

Joining tables is where SQL starts feeling genuinely powerful — and where a subtle mistake silently produces wrong results with zero errors. This lesson covers all six JOIN types with real output differences, then spends real time on the single bug that catches nearly everyone at least once.

The Core Two

-- INNER JOIN: only rows that match in both tables
SELECT d.full_name, t.fare_usd
FROM dbo.Driver d
INNER JOIN dbo.Trip t ON d.driver_id = t.driver_id;

-- LEFT JOIN: all rows from the left table, matched rows from the right (NULL if no match)
SELECT d.full_name, t.fare_usd
FROM dbo.Driver d
LEFT JOIN dbo.Trip t ON d.driver_id = t.driver_id;

Concretely, if a driver named ‘Amir Khan’ exists but has never had a trip logged: INNER JOIN omits him from the result entirely; LEFT JOIN still shows one row for him, with fare_usd as NULL. That NULL is the entire reason LEFT JOIN exists — it’s how you answer “show me every driver, including ones with zero trips.”

The Rest of the Set

-- RIGHT JOIN: mirror of LEFT — all rows from the right table instead
SELECT d.full_name, t.fare_usd FROM dbo.Trip t RIGHT JOIN dbo.Driver d ON d.driver_id = t.driver_id;

-- FULL OUTER JOIN: everything from both sides, matched where possible
SELECT d.full_name, t.trip_id FROM dbo.Driver d FULL OUTER JOIN dbo.Trip t ON d.driver_id = t.driver_id;

-- CROSS JOIN: every combination (Cartesian product) — rarely intentional by accident
SELECT d.full_name, x.label FROM dbo.Driver d CROSS JOIN (VALUES ('Gold'),('Silver')) AS x(label);

-- SELF JOIN: a table joined to itself — e.g. drivers sharing a city
SELECT d1.full_name, d2.full_name, d1.city
FROM dbo.Driver d1 INNER JOIN dbo.Driver d2 ON d1.city = d2.city AND d1.driver_id < d2.driver_id;

In practice, RIGHT JOIN is rarely used on purpose — anything expressible with RIGHT JOIN can be rewritten as a LEFT JOIN by swapping which table comes first, and most style guides prefer that for consistency. FULL OUTER JOIN is genuinely useful for reconciliation tasks ("what's in table A but not B, and vice versa, in one query"). CROSS JOIN's real, non-accidental use case is generating combinations — like every product paired with every size, before either exists in a real order.

The SELF JOIN's d1.driver_id < d2.driver_id condition deserves its own note: without it, every pair of same-city drivers would appear twice (Amir/Priya and Priya/Amir), plus every driver paired with themselves. The inequality keeps exactly one direction of each unique pair.

The Bug That Gets Everyone at Least Once

Using LEFT JOIN correctly, then adding a WHERE filter on the right-hand table's column, silently turns it back into an INNER JOIN:

-- BUG: this discards the NULL rows LEFT JOIN was specifically trying to preserve
SELECT d.full_name, t.fare_usd
FROM dbo.Driver d LEFT JOIN dbo.Trip t ON d.driver_id = t.driver_id
WHERE t.fare_usd > 20;

NULL fails the > 20 comparison (from Chapter 3's three-valued-logic rule), so unmatched drivers with no trips disappear from the result — exactly what LEFT JOIN was meant to prevent. The query runs without error and looks completely reasonable; you only notice something's wrong when a driver you know exists is mysteriously missing from a report.

-- The fix: move the condition into the ON clause instead of WHERE
SELECT d.full_name, t.fare_usd
FROM dbo.Driver d LEFT JOIN dbo.Trip t ON d.driver_id = t.driver_id AND t.fare_usd > 20;
-- Now unmatched drivers still appear (fare_usd NULL); only trips are filtered before the join completes