SQL Joins Cheat Sheet

WITH customers (id, name) AS (
  VALUES (1, 'Ada'), (2, 'Grace')
), orders (id, customer_id) AS (
  VALUES (101, 1), (102, 1), (103, 3)
)
SELECT o.id, c.name
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

id  | name
----+-----
101 | Ada
102 | Ada

WITH orders (order_id) AS (VALUES (101), (102)),
shipments (order_id, status) AS (VALUES (101, 'sent'), (103, 'pending'))
SELECT order_id, status
FROM orders
JOIN shipments USING (order_id);

order_id | status
---------+-------
101      | sent

WITH customers (id, name) AS (
  VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus')
), orders (id, customer_id, status) AS (
  VALUES (101, 1, 'paid'), (102, 1, 'pending'), (103, 2, 'paid')
)
SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id;

name  | id
------+----
Ada   | 101
Grace | 103
Linus | NULL

WITH customers (id) AS (VALUES (1), (2), (3)),
orders (id, customer_id) AS (VALUES (101, 1), (102, 2))
SELECT c.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

id
--
3

WITH customers (id, name) AS (VALUES (1, 'Ada')),
orders (id, customer_id) AS (VALUES (101, 1), (102, 9))
SELECT c.name, o.id
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id;

name | id
-----+----
Ada  | 101
NULL | 102

WITH app_users (email) AS (VALUES ('ada@example.com'), ('linus@example.com')),
subscribers (email) AS (VALUES ('ada@example.com'), ('grace@example.com'))
SELECT COALESCE(a.email, s.email) AS email,
       a.email IS NOT NULL AS has_account,
       s.email IS NOT NULL AS subscribes
FROM app_users AS a
FULL OUTER JOIN subscribers AS s ON s.email = a.email
ORDER BY email;

email             | has_account | subscribes
------------------+-------------+-----------
ada@example.com   | true        | true
grace@example.com | false       | true
linus@example.com | true        | false

Note: RIGHT JOIN and FULL OUTER JOIN are available in PostgreSQL but are not implemented by every SQL database.

WITH colors (color) AS (VALUES ('blue'), ('green')),
sizes (size) AS (VALUES ('S'), ('M'))
SELECT c.color, s.size
FROM colors AS c
CROSS JOIN sizes AS s
ORDER BY c.color, s.size;

color | size
------+-----
blue  | M
blue  | S
green | M
green | S

WITH employees (id, name, manager_id) AS (
  VALUES (1, 'Ada', NULL), (2, 'Grace', 1), (3, 'Linus', 1)
)
SELECT e.name, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.id;

name  | manager
------+--------
Ada   | NULL
Grace | Ada
Linus | Ada

WITH customers (id, name) AS (VALUES (1, 'Ada')),
products (id, name) AS (VALUES (10, 'Keyboard')),
orders (id, customer_id, product_id) AS (VALUES (101, 1, 10))
SELECT o.id, c.name AS customer, p.name AS product
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products AS p ON p.id = o.product_id;

id  | customer | product
----+----------+---------
101 | Ada      | Keyboard

WITH inventory (sku, region) AS (VALUES ('A-1', 'EU'), ('A-1', 'US')),
prices (sku, region, price) AS (VALUES ('A-1', 'EU', 12.00), ('A-1', 'US', 10.00))
SELECT i.sku, i.region, p.price
FROM inventory AS i
JOIN prices AS p ON p.sku = i.sku AND p.region = i.region
ORDER BY i.region;

WITH customers (id) AS (VALUES (1), (2)),
orders (id, customer_id, total) AS (VALUES (101, 1, 25.00), (102, 1, 15.00))
SELECT c.id,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS revenue
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.id;

id | order_count | revenue
---+-------------+--------
1  | 2           | 40.00
2  | 0           | 0

WITH customers (id) AS (VALUES (1), (2)),
orders (id, customer_id, created_at) AS (
  VALUES (101, 1, DATE '2026-01-01'), (102, 1, DATE '2026-02-01')
)
SELECT c.id AS customer_id, latest.id AS latest_order_id
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.id
  FROM orders AS o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 1
) AS latest ON true
ORDER BY c.id;

customer_id | latest_order_id
------------+----------------
1           | 102
2           | NULL

Note: LATERAL is a PostgreSQL-supported SQL feature; check the target database before relying on the exact syntax.