SQL Joins Reference

Join types, anti-join patterns, multi-table joins, and when to use each.

INNER JOIN

Returns only rows that match in both tables.

Match orders to customers

SELECT o.id, o.total, c.name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

If a customer has no orders, they do not appear. If an order has no matching customer (orphaned FK), it does not appear.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, with NULLs where the right table has no match.

All customers, even those without orders

SELECT c.name, c.email, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Find customers who have never ordered (anti-join pattern)

SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

The anti-join is one of the most useful patterns in SQL. The LEFT JOIN + WHERE NULL trick finds "rows in A that have no corresponding row in B."

RIGHT JOIN

Returns all rows from the right table. Rarely used — rewrite as LEFT JOIN with the tables swapped for readability.

-- These two queries are identical
SELECT * FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id;
SELECT * FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;

Prefer LEFT JOIN. It reads top-to-bottom: "start with this table, bring in extras from that table."

FULL OUTER JOIN

Returns all rows from both tables, with NULLs on either side where there is no match.

Find mismatches between two inventories

SELECT
    COALESCE(a.hostname, b.hostname) AS hostname,
    a.ip_address AS cmdb_ip,
    b.ip_address AS scan_ip
FROM cmdb_devices a
FULL OUTER JOIN scan_results b ON a.hostname = b.hostname
WHERE a.hostname IS NULL OR b.hostname IS NULL;

This finds devices in CMDB but not scanned, and scanned devices not in CMDB. Useful for inventory reconciliation.

SQLite does not support FULL OUTER JOIN. Emulate with UNION of LEFT JOIN and RIGHT JOIN (or two LEFT JOINs reversed).

CROSS JOIN

Cartesian product: every row from A paired with every row from B.

Generate all combinations

SELECT v.id AS vlan_id, s.hostname
FROM vlans v
CROSS JOIN switches s;

Use sparingly. A cross join of two 1000-row tables produces 1,000,000 rows.

Self-Join

Join a table to itself. Common for hierarchical data or comparing rows within the same table.

Org chart: employees and their managers

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Find duplicate entries

SELECT a.id, b.id, a.email
FROM users a
JOIN users b ON a.email = b.email AND a.id < b.id;

The a.id < b.id prevents pairing a row with itself and avoids reporting each pair twice.

Multi-Table Joins

Chain joins for related data

SELECT
    o.id AS order_id,
    c.name AS customer,
    p.name AS product,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2026-01-01'
ORDER BY o.id;

Read multi-join queries as a chain: start from the "anchor" table (orders), then follow foreign keys outward.

JOIN with Aggregation

Count and sum across joined tables

SELECT c.name,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

Use LEFT JOIN + COALESCE to include customers with zero orders (COUNT returns 0, SUM returns NULL without COALESCE).

USING Shorthand

When the join column has the same name in both tables, USING is shorter than ON.

-- Equivalent queries
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
SELECT * FROM orders JOIN customers USING (customer_id);

USING also deduplicates the join column in the output — it appears once instead of twice.

NATURAL JOIN

Joins on all columns with matching names. Avoid this — it is fragile. Adding a column with a common name (like name or status) silently changes the join condition.

-- Dangerous: joins on ALL shared column names
SELECT * FROM orders NATURAL JOIN customers;

-- Explicit is always safer
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;