NexusCS

SQL joins

Databases
Quick reference for SQL JOIN types, syntax, and patterns.

Getting started

Basic INNER JOIN

SELECT *
FROM customers c
INNER JOIN orders o
  ON c.id = o.customer_id;

Returns only matching rows.

Basic LEFT JOIN

SELECT *
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id;

Returns all left rows + matches.

JOIN shorthand

JOIN        -- Same as INNER JOIN
LEFT JOIN   -- Same as LEFT OUTER JOIN
RIGHT JOIN  -- Same as RIGHT OUTER JOIN
FULL JOIN   -- Same as FULL OUTER JOIN

Keywords INNER and OUTER are optional.

JOIN types

Visual overview

INNER JOIN          LEFT JOIN           RIGHT JOIN
┌────────┐         ┌─────────┐         ┌─────────┐
│ A  ┌───┼──┐      │ A  ┌────┼──┐      │   ┌─────┼─┐
│    │ ∩ │ B│      │    │ ∩  │ B│      │ A │  ∩  │B│
└────┼───┘  │      └────┼────┘  │      │   └─────┼─┘
     └──────┘           └───────┘      └─────────┘
     ∩ only           A + ∩            ∩ + B


FULL OUTER JOIN     CROSS JOIN         ANTI-JOIN
┌─────────┐         ┌─────┐┌─────┐    ┌─────────┐
│ A  ┌────┼──┐      │  A  ││  B  │    │ A  ┌────┼──┐
│    │ ∩  │ B│      │     ││     │    │    │ ∅  │ B│
└────┼────┘  │      └─────┘└─────┘    └────┼────┘  │
     └───────┘         A × B               └───────┘
   A + ∩ + B                             A only

JOIN types table

Type Returns Use case
INNER JOIN Matching rows only Related data
LEFT JOIN All left + matches Optional relation
RIGHT JOIN All right + matches Reverse of LEFT
FULL OUTER JOIN All rows both tables Complete merge
CROSS JOIN Cartesian product All combinations
SELF JOIN Table joined to itself Hierarchies
NATURAL JOIN Auto-match columns ⚠️ Risky

JOIN syntax

INNER JOIN

SELECT a.name, b.total
FROM customers a
INNER JOIN orders b
  ON a.id = b.customer_id;

LEFT JOIN

SELECT a.name, b.total
FROM customers a
LEFT JOIN orders b
  ON a.id = b.customer_id;

NULL for non-matches.

RIGHT JOIN

SELECT a.name, b.total
FROM customers a
RIGHT JOIN orders b
  ON a.id = b.customer_id;

NULL for non-matches.

FULL OUTER JOIN

SELECT a.name, b.total
FROM customers a
FULL OUTER JOIN orders b
  ON a.id = b.customer_id;

NULL on both sides possible.

CROSS JOIN

SELECT a.color, b.size
FROM colors a
CROSS JOIN sizes b;

N × M rows (Cartesian).

SELF JOIN

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

Requires table aliases.

Advanced patterns

Anti-join (no match)

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

Finds customers with NO orders.

Semi-join (EXISTS)

SELECT *
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

Customers with at least one order.

Multiple conditions

SELECT *
FROM orders o
JOIN products p
  ON o.product_id = p.id
  AND o.warehouse = p.warehouse;

Non-equi join (range)

SELECT *
FROM orders o
JOIN discounts d
  ON o.total BETWEEN d.min AND d.max;

USING clause

SELECT *
FROM orders
JOIN customers USING (customer_id);

De-duplicates the join column.

Multiple table join

SELECT c.name, o.date, i.product
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN items i ON o.id = i.order_id;

Gotchas

Common pitfalls

Issue Problem Fix
NULL match NULL = NULL is false Use IS NULL
Cartesian Missing ON clause Add join condition
Duplicates Multiple matches Use DISTINCT
Ambiguous Same column name Use table alias
Filter order WHERE vs ON in outer ON for join, WHERE for filter

NULL handling

-- ❌ This won't match NULLs
SELECT * FROM a
JOIN b ON a.val = b.val;

-- ✅ Match including NULLs
SELECT * FROM a
JOIN b ON a.val = b.val
   OR (a.val IS NULL AND b.val IS NULL);

ON vs WHERE in LEFT JOIN

-- ON: filters during join
SELECT * FROM a
LEFT JOIN b ON a.id = b.id
  AND b.status = 'active';  -- b can still be NULL

-- WHERE: filters after join
SELECT * FROM a
LEFT JOIN b ON a.id = b.id
WHERE b.status = 'active';  -- removes NULL rows

Performance

Join algorithms

Algorithm Best for Index?
Nested Loops Small outer + indexed inner Yes
Hash Join Large unsorted tables No
Merge Join Sorted inputs Helpful

Index recommendations

-- Index the join columns
CREATE INDEX idx_orders_customer
  ON orders(customer_id);

-- Composite for multi-condition
CREATE INDEX idx_orders_cust_status
  ON orders(customer_id, status);

Database differences

SQLite limitations

-- ❌ Not supported in SQLite
RIGHT JOIN
FULL OUTER JOIN

-- ✅ Workaround for RIGHT JOIN
SELECT * FROM b LEFT JOIN a ON ...

-- ✅ Workaround for FULL OUTER
SELECT * FROM a LEFT JOIN b ON ...
UNION ALL
SELECT * FROM b LEFT JOIN a ON ...
WHERE a.id IS NULL;

PostgreSQL LATERAL

-- Reference preceding tables
SELECT *
FROM orders o
LEFT JOIN LATERAL (
  SELECT * FROM items
  WHERE order_id = o.id
  LIMIT 5
) i ON true;

Examples

Sample schema

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  country VARCHAR(50)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total DECIMAL(10,2),
  order_date DATE
);

Real-world queries

-- All customers with their orders (or NULL)
SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- Customers who never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- Customers from same country
SELECT a.name, b.name, a.country
FROM customers a
JOIN customers b ON a.country = b.country
  AND a.id < b.id;

-- Orders with customer and items
SELECT c.name, o.order_date, i.product
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN items i ON o.id = i.order_id
WHERE o.order_date >= '2024-01-01';

Also see