SQL Join Basics and Join Types

SQL Joins•Permalink

Core SQL join types with practical examples, NULL behavior, and quick rules for correct join selection.

View

Standard Detailed Compact

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.

INNER JOIN

Return only rows that match on both sides.

↗#

sqlANYinner-joinmatching-rows

sql

SELECT c.customer_id, c.name, o.order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;

Use `INNER JOIN` when you only want records with matching keys in both tables.

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;

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;

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;

Useful for reconciliation reports and data comparison. Some databases require emulation instead of native support.

CROSS JOIN

Produce the Cartesian product of two tables.

↗#

sqlANYcross-joincartesian-product

sql

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

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.name

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';

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.

Recommended next

No recommendations yet.

Browse all sheets →