Joins

A join combines rows from two or more tables based on a related column. Joins are the mechanism that makes relational databases powerful: instead of duplicating data, you store it once and link it together at query time.

INNER JOIN

An INNER JOIN returns only the rows where the join condition matches in both tables:

-- Return orders along with the customer name for each
SELECT orders.id, customers.name, orders.total
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

You can use table aliases to keep queries concise:

SELECT o.id, c.name, o.total
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE o.total > 100
ORDER BY o.total DESC;

LEFT JOIN

A LEFT JOIN (also called LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If there is no match, the right-side columns contain NULL:

-- All customers, including those who have never placed an order
SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY order_count DESC;

This query reveals customers with zero orders because their order_count will be 0 (COUNT ignores NULL values).

RIGHT JOIN and FULL OUTER JOIN

SQLite supports RIGHT JOIN and FULL OUTER JOIN syntax (added in version 3.39.0). Before that version, you achieve the same result by swapping table order in a LEFT JOIN:

-- Equivalent to: SELECT ... FROM orders RIGHT JOIN customers ...
SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

Joining Multiple Tables

You can chain joins to combine more than two tables:

SELECT o.id AS order_id,
       c.name AS customer,
       p.name AS product,
       oi.quantity,
       oi.unit_price
FROM order_items AS oi
INNER JOIN orders   AS o ON oi.order_id   = o.id
INNER JOIN customers AS c ON o.customer_id = c.id
INNER JOIN products  AS p ON oi.product_id = p.id
ORDER BY o.id, p.name;

Self Join

A self join joins a table to itself. This is useful for hierarchical data or comparing rows within the same table:

-- Employees table with a manager_id referencing another employee
SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id;

Cross Join