Introduction to SQL JOINs

Understand how tables relate to each other through keys, and why queries combine them with JOINs.

JOINs combine rows from two or more tables based on related columns. They're fundamental to working with relational databases.

Two tables, users and orders, with lines from each orders.user_id value to the users.id value it points at — Each orders.user_id value points at one users.id. That link is the foreign key a JOIN follows.

Why Use JOINs?

In relational databases, data is split across multiple tables to avoid duplication. JOINs reunite this data when you need it.

Example Tables

users

id	name	email
1	Alice	alice@mail.com
2	Bob	bob@mail.com
3	Carol	carol@mail.com

orders

id	user_id	total
101	1	$150
102	1	$75
103	2	$200

The Relationship

The user_id column in orders references the id column in users. This is called a foreign key relationship.

JOIN Types Overview

Type	Returns
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from left + matches from right
RIGHT JOIN	All rows from right + matches from left
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Every combination (Cartesian product)

Note: JOIN without a prefix defaults to INNER JOIN.

Create the Sample Tables

The JOIN guides use these two tables. The statements run in SQLite, PostgreSQL, MySQL and SQL Server. Run them once, then try each query in the guides.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total NUMERIC NOT NULL
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Alice', 'alice@mail.com'),
  (2, 'Bob', 'bob@mail.com'),
  (3, 'Carol', 'carol@mail.com');

INSERT INTO orders (id, user_id, total) VALUES
  (101, 1, 150),
  (102, 1, 75),
  (103, 2, 200);

Version note: RIGHT JOIN and FULL OUTER JOIN work in SQLite 3.39.0 and later, in PostgreSQL and in SQL Server. MySQL supports RIGHT JOIN but not FULL OUTER JOIN.