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

INNER JOIN

↗#

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

# Return only rows that match on both sides.

LEFT JOIN

↗#

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

# Keep all rows from the left table and match rows from the right when available.

RIGHT JOIN

↗#

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

# Keep all rows from the right table and match rows from the left when available.

FULL OUTER JOIN

↗#

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

# Keep all rows from both tables and match where possible.

CROSS JOIN

↗#

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

# Produce the Cartesian product of two tables.

## Quick rules

Join on stable keys

↗#

Good:   orders.customer_id = customers.customer_id
Risky:  orders.customer_name = customers.name

# Prefer primary keys and foreign keys over names or free-form text.

Put right-side filters in ON when preserving outer rows

↗#

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

# A `WHERE` clause can accidentally turn a left join into an inner join.

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 →