SQL JOIN Cheat Sheet

Quick reference for all SQL JOIN types with visual diagrams and code examples

INNER JOIN MOST COMMON

Returns only rows that have matching values in both tables. This is the default JOIN type and most commonly used.

SELECT orders.id, customers.name, orders.amount
FROM orders
INNER JOIN customers
  ON orders.customer_id = customers.id

Key Points:

Only returns matching records from both tables
Filters out non-matching rows automatically
Most efficient JOIN for performance
Can be written as just JOIN (INNER is implied)

📚 Learn more: SQL JOINs for Beginners, MySQL Joins Guide, SQL Server Joins Guide

LEFT JOIN

Returns all rows from the left table and matching rows from the right table. If no match exists, NULL values are returned for right table columns.

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id

Key Points:

Preserves all records from the left (first) table
NULL values appear for non-matching right table columns
Also known as LEFT OUTER JOIN
Useful for finding records without related data

📚 Learn more: SQL JOINs for Beginners, MySQL Joins Guide, SQL Server Joins Guide

RIGHT JOIN

Returns all rows from the right table and matching rows from the left table. The opposite of LEFT JOIN.

SELECT orders.amount, customers.name
FROM orders
RIGHT JOIN customers
  ON orders.customer_id = customers.id

Key Points:

Preserves all records from the right (second) table
NULL values appear for non-matching left table columns
Also known as RIGHT OUTER JOIN
Can always be rewritten as a LEFT JOIN

📚 Learn more: SQL JOINs for Beginners, MySQL Joins Guide, SQL Server Joins Guide

FULL OUTER JOIN

Returns all rows from both tables, with NULL values where there are no matches in either direction.

SELECT customers.name, orders.amount
FROM customers
FULL OUTER JOIN orders
  ON customers.id = orders.customer_id

Key Points:

Combines results of both LEFT and RIGHT JOINs
Shows all records from both tables
Not supported in MySQL (use UNION instead)
Useful for finding all relationships and gaps

📚 Learn more: SQL JOINs for Beginners, MySQL Full Outer Join, SQL Server Joins Guide

CROSS JOIN USE WITH CAUTION

Returns the Cartesian product of both tables - every row from the first table combined with every row from the second table.

SELECT sizes.name, colors.name
FROM sizes
CROSS JOIN colors

-- Alternative syntax
SELECT sizes.name, colors.name
FROM sizes, colors

Key Points:

No ON clause needed (no join condition)
Result has rows = (left table rows × right table rows)
Can quickly produce huge result sets
Useful for generating combinations or test data

📚 Learn more: SQL JOINs for Beginners, PostgreSQL Cross Join

LEFT ANTI JOIN

Returns rows from the left table that have no matching rows in the right table. Useful for finding records without relationships.

SELECT customers.name
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
WHERE orders.id IS NULL

-- Alternative: NOT EXISTS
SELECT customers.name
FROM customers
WHERE NOT EXISTS (
  SELECT 1 FROM orders
  WHERE orders.customer_id = customers.id
)

Key Points:

Implemented using LEFT JOIN + WHERE IS NULL
Finds records without related data
Also called "LEFT EXCLUDING JOIN"
Example: customers who never placed an order

RIGHT ANTI JOIN

Returns rows from the right table that have no matching rows in the left table. The opposite of LEFT ANTI JOIN.

SELECT orders.id
FROM customers
RIGHT JOIN orders
  ON customers.id = orders.customer_id
WHERE customers.id IS NULL

-- Usually rewritten as LEFT ANTI JOIN
SELECT orders.id
FROM orders
LEFT JOIN customers
  ON orders.customer_id = customers.id
WHERE customers.id IS NULL

Key Points:

Implemented using RIGHT JOIN + WHERE IS NULL
Finds orphaned records in the right table
Can always be rewritten as LEFT ANTI JOIN
Example: orders without a valid customer

Need a Better Way to Write SQL?

Beekeeper Studio is a modern, intuitive SQL editor with autocomplete, query history, and support for 15+ databases.

Try Beekeeper Studio Free