SQL JOIN Cheatsheet - SQL JOIN Query Reference

For developers who join tables but still get the row counts wrong. The recurring trap is that a JOIN on a one-to-many parent duplicates parent rows, inflating results unless you dedupe. By the end you can pick INNER vs LEFT by whether un-matched rows should survive, understand the duplication pitfall on one-to-many joins, express candidate-missing conditions with LEFT JOIN ... WHERE ... IS NULL, and choose EXISTS for correlated subqueries.

Databases·41 commands·Last updated 2026-07-21

INNER JOIN 5

SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id

Return only matched rows from both tables

SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id

Short form JOIN (defaults to INNER)

SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id AND o.status = 'paid'

Multiple ON conditions jointly decide the match

SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE o.total > 100

Filter the whole result with WHERE after JOIN

SELECT u.name, o.total, p.amount FROM users u JOIN orders o ON u.id = o.user_id JOIN payments p ON o.id = p.order_id

Chained multi-table INNER JOIN

LEFT / RIGHT JOIN 6

SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id

All left rows; right NULL if no match

SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id

All right rows; left NULL if no match

SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE orders.id IS NULL

Anti-join: left-only rows (users with no orders)

SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid'

ON condition in LEFT JOIN does not drop left rows

SELECT * FROM orders o RIGHT JOIN users u ON u.id = o.user_id WHERE o.id IS NULL

RIGHT JOIN anti-join (users with no orders)

SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id

LEFT JOIN + aggregate, count users incl. zero orders

FULL / CROSS JOIN 5

SELECT * FROM users FULL OUTER JOIN orders ON users.id = orders.user_id

All rows from both; NULL where unmatched

SELECT * FROM users u FULL JOIN orders o ON u.id = o.user_id WHERE u.id IS NULL OR o.user_id IS NULL

Symmetric difference (rows unique to each side)

SELECT * FROM table1 CROSS JOIN table2

Cartesian product

SELECT * FROM table1, table2

Implicit CROSS JOIN (comma)

SELECT * FROM sizes CROSS JOIN colors

All combinations (size × color)

Self & Multi-table 5

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

Self-join (employee-manager)

SELECT a.name, b.name AS parent FROM categories a JOIN categories b ON a.parent_id = b.id

Self-join (category-parent)

SELECT * FROM a JOIN b ON a.id = b.a_id JOIN c ON b.id = c.b_id

Three-table join

SELECT t1.name AS grandchild, t3.name AS grandparent FROM tree t1 JOIN tree t2 ON t1.parent_id = t2.id JOIN tree t3 ON t2.parent_id = t3.id

Multi-level self-join (3 generations)

SELECT u.name, o.total, p.amount FROM users u JOIN orders o ON u.id = o.user_id JOIN payments p ON o.id = p.order_id

Three-table (user-order-payment)

JOIN & Aggregation 5

SELECT u.name, COUNT(o.id) AS order_count FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id

Order count per user

SELECT u.name, SUM(o.total) AS total_spent FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id HAVING SUM(o.total) > 1000

HAVING filters aggregates (spent > 1000)

SELECT c.name, AVG(o.total) AS avg_order FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.id

Group average

SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id HAVING COUNT(o.id) = 0

LEFT JOIN + HAVING for zero-order users

SELECT DATE(o.created_at) AS d, COUNT(*) FROM orders o JOIN users u ON u.id = o.user_id GROUP BY DATE(o.created_at)

Group by date

JOIN Performance 6

EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id

Show execution plan

EXPLAIN ANALYZE SELECT * FROM users u JOIN orders o ON u.id = o.user_id

Run & show timings (PostgreSQL / MySQL 8.0+)

CREATE INDEX idx_orders_user_id ON orders(user_id)

Index foreign key to speed up JOIN

SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id

Select only needed columns (avoid SELECT *)

SELECT * FROM users u STRAIGHT_JOIN orders o ON u.id = o.user_id

MySQL: force left table as driver

SELECT * FROM users u JOIN orders o FORCE INDEX(idx_user_id) ON u.id = o.user_id

MySQL: force a specific index

USING & Natural Join 4

SELECT * FROM users JOIN orders USING (user_id)

USING shorthand (same-named join column)

SELECT * FROM users u JOIN orders o USING (user_id, tenant_id)

USING multiple columns (merges same-named col)

SELECT * FROM users NATURAL JOIN orders

Natural join (auto-match all same-named cols; use with care)

SELECT * FROM users u JOIN orders o ON u.id = o.user_id AND u.tenant_id = o.tenant_id

Multi-column join (can use USING)

Subquery & EXISTS 5

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)

Users who have orders

SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)

Users with no orders

SELECT * FROM orders o WHERE o.user_id IN (SELECT id FROM users WHERE status = 'active')

IN subquery filter

SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt FROM users u

Correlated subquery (scalar)

SELECT * FROM orders WHERE user_id = (SELECT MAX(user_id) FROM orders)

Subquery returning a single value

Tips

A LEFT JOIN WHERE condition in ON only affects right-table matching; in WHERE it filters the whole result set.
CROSS JOIN produces a Cartesian product — use cautiously on large data.
Table aliases (u, o) make multi-table queries more concise and readable.
EXISTS subqueries can outperform JOIN in some cases, especially when only existence matters.
Index the JOIN columns, otherwise a full table scan hurts performance.
USING(a, b) is cleaner than ON x.a = y.a AND x.b = y.b, and merges same-named columns.

Official References

Each command links to its official documentation below, so you can verify the latest usage and read deeper.

Official Docs

Maintained by LaoHand

Publicly updated on Jul 21, 2026, continuously proofread against official docs.

Contact Us

Wrong command or description? Send us corrections, business inquiries or product feedback by email.