SQL Joins Explained: INNER, LEFT, RIGHT, FULL, CROSS, and Self Joins

SQL joins confuse developers because “LEFT” and “RIGHT” refer to positions in the query, not logical directions. The correct mental model is: which rows from which table do you want to keep when there’s no match? Once that clicks, all join types become intuitive.

⚡ TL;DR: INNER: only rows that match in BOTH tables. LEFT: all rows from left table, nulls where right has no match. RIGHT: all rows from right table, nulls where left has no match. FULL OUTER: all rows from both, nulls where no match. CROSS: every combination. Self join: table joined with itself.

INNER JOIN — only matching rows

-- Returns only rows where BOTH tables have a match
SELECT u.name, o.total, o.status
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
-- Result: only users WHO HAVE orders
-- Users without orders: NOT included
-- Orders without users: NOT included (shouldn't happen with FK)

-- Multiple joins:
SELECT u.name, p.title, c.text
FROM users u
INNER JOIN posts p ON p.author_id = u.id
INNER JOIN comments c ON c.post_id = p.id
WHERE u.active = true
ORDER BY p.created_at DESC;

LEFT JOIN — all rows from left table

-- All users, with their orders (NULL if no order)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- Users with 0 orders: order_count = 0 (COUNT ignores NULLs)

-- Find users WITHOUT orders:
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;  -- NULL means no matching order

-- LEFT JOIN = LEFT OUTER JOIN (same thing)

FULL OUTER JOIN and CROSS JOIN

-- FULL OUTER: all rows from both tables
SELECT a.id AS a_id, b.id AS b_id
FROM table_a a
FULL OUTER JOIN table_b b ON b.ref_id = a.id;
-- Rows only in a: b columns are NULL
-- Rows only in b: a columns are NULL

-- CROSS JOIN: cartesian product (every combo)
SELECT size, color
FROM sizes CROSS JOIN colors;
-- 5 sizes × 8 colors = 40 rows
-- Use case: generate all combinations (product variants)

Self join — join table with itself

-- Employee manager hierarchy
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- LEFT JOIN: include employees without managers (CEO)

-- Find users who made orders on same day as another user:
SELECT DISTINCT a.user_id, b.user_id, a.order_date
FROM orders a
JOIN orders b ON a.order_date = b.order_date
  AND a.user_id != b.user_id;

• ✅ INNER JOIN for related data that must exist on both sides
• ✅ LEFT JOIN for optional relationships (user may have 0 orders)
• ✅ Use LEFT JOIN + WHERE right.id IS NULL to find unmatched rows
• ✅ CROSS JOIN for generating combinations
• ✅ Self join for hierarchical data (employee/manager, category/subcategory)
• ❌ Never do CROSS JOIN on large tables without filtering

External reference: Use The Index, Luke — joins.