Join Introduction

Most real data lives across multiple tables.

Joins let you combine those tables into one meaningful result set.

Core Concepts

flowchart LR
  A[(customers)] --> C[Join on matching key]
  B[(orders)] --> C
  C --> D[Combined result rows]

Join type	What it returns
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All left-table rows + matching right rows
CROSS JOIN	Every combination of left and right rows
SELF JOIN	A table joined to itself

INNER vs LEFT

• Use INNER JOIN when you only want rows with matches in both tables.
• Use LEFT JOIN when you need all rows from the left table, even without matches.

Join Key Rule

• Join on stable keys (customer_id, order_id), not loosely matching text.
• Keep join predicates explicit with ON.

Code Examples

-- Setup small sample tables.
CREATE TABLE customers_join (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);

CREATE TABLE orders_join (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total        REAL NOT NULL
);

INSERT INTO customers_join (customer_id, name)
VALUES (1, 'Ada'), (2, 'Alan');

INSERT INTO orders_join (order_id, customer_id, total)
VALUES (101, 1, 49.99), (102, 1, 19.50), (103, 2, 75.00);

Expected output
Tables created and sample rows inserted successfully.

-- Basic join: show orders with customer names.
SELECT c.name, o.order_id, o.total
FROM customers_join AS c
INNER JOIN orders_join AS o
  ON c.customer_id = o.customer_id
ORDER BY o.order_id;

name	order_id	total
Ada	101	49.99
Ada	102	19.50
Alan	103	75.00

SQLite-Specific Nuances

SQLite Nuance

SQLite supports standard join syntax (INNER, LEFT, CROSS) with familiar ON predicates.

As with all SQL, output row order is only guaranteed when you use ORDER BY.

Common Pitfalls / Best Practices

Pitfall

Joining tables without an ON condition, which can create accidental Cartesian products.

Best Practice

Always start by writing the join keys first (ON left.id = right.left_id), then add selected columns.

Quick Challenge

Write a join that returns order_id and customer name from the sample tables.

View Solution

SELECT o.order_id, c.name
FROM orders_join AS o
INNER JOIN customers_join AS c
  ON o.customer_id = c.customer_id
ORDER BY o.order_id;

order_id	name
101	Ada
102	Ada
103	Alan