sql
SELECT c.customer_id, c.name, o.order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;Notes
Use `INNER JOIN` when you only want records with matching keys in both tables.
SQL Join Basics and Join Types
Core SQL join types with practical examples, NULL behavior, and quick rules for correct join selection.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Join types at a glance
The essential join shapes every developer should know.
LEFT JOIN
Keep all rows from the left table and match rows from the right when available.
sqlANYleft-joinnulls
sql
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;Notes
Rows from `customers` without a matching order still appear, with right-side columns as `NULL`.
RIGHT JOIN
Keep all rows from the right table and match rows from the left when available.
sqlANYright-join
sql
SELECT c.customer_id, c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;Notes
Many teams prefer rewriting this as a `LEFT JOIN` by swapping table order for readability.
FULL OUTER JOIN
Keep all rows from both tables and match where possible.
sqlANYfull-outer-joinreconciliation
sql
SELECT c.customer_id, c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;Notes
Useful for reconciliation reports and data comparison. Some databases require emulation instead of native support.
sql
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;Notes
Use carefully. Output size becomes `left_rows × right_rows`.
Quick rules
Fast mental models that prevent common mistakes.
Join on stable keys
Prefer primary keys and foreign keys over names or free-form text.
sqlANYkeysbest-practice
sql
Good: orders.customer_id = customers.customer_id
Risky: orders.customer_name = customers.nameNotes
Keys are usually unique, indexed, and less error-prone than textual attributes.
Put right-side filters in ON when preserving outer rows
A `WHERE` clause can accidentally turn a left join into an inner join.
sqlANYleft-joinon-vs-where
sql
-- Keep all customers, only match paid orders
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'paid';Notes
When using outer joins, filters on the optional side often belong inside the `ON` clause.
More in SQL Joins
SQL Join Patterns and Pitfalls
Common SQL join mistakes, reusable templates, and correctness rules for production queries.
Join Performance, EXPLAIN, and Optimization
SQL join tuning with indexes, plan inspection, row reduction, and troubleshooting guidance.
Aggregation After Joins and NULL Handling
Counting, summing, pre-aggregating, and handling NULLs correctly after one-to-many and outer joins.
Semi-Joins and Anti-Joins
High-value EXISTS, IN, NOT EXISTS, and LEFT JOIN IS NULL patterns for membership and missing-row queries.
Many-to-Many Joins and Bridge Tables
Many-to-many SQL join patterns with bridge tables, aggregation, filtering, and index guidance.
Self Joins and Hierarchies
SQL self join examples for managers, duplicates, comparisons, and basic hierarchy modeling.