Joins in SQLite

Joins combine rows from two or more tables based on a related column. SQLite supports the standard join types, and as of SQLite 3.39.0 (released June 2022) that list includes RIGHT JOIN and FULL OUTER JOIN, which were missing for most of SQLite's history. If you're on an older build, you'll need the workarounds shown later. Check your version with SELECT sqlite_version();.

The examples below use two tables:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name TEXT
);
 
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  product TEXT,
  amount REAL
);

INNER JOIN

Returns only rows that have a match in both tables. Rows with no match on either side are dropped.

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

The INNER keyword is optional. Writing just JOIN means the same thing in SQLite. Be explicit if your team reads the queries later.

LEFT JOIN

Returns every row from the left table, plus matching rows from the right table. Where there is no match, the right-side columns come back as NULL.

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

This is how you find customers who have never ordered: keep only the rows where the right side is null (see anti-joins below). LEFT OUTER JOIN is a synonym; the OUTER keyword adds nothing.

CROSS JOIN

Produces the Cartesian product: every row of the left table paired with every row of the right table. No ON clause. Use it deliberately, because the row count is the product of the two table sizes.

SELECT c.name, o.product
FROM customers c
CROSS JOIN orders o;

A common legitimate use is generating combinations, for example pairing every product with every size from a small lookup table. Note that in SQLite, CROSS JOIN also suppresses the query planner's freedom to reorder the tables, which can occasionally be used as an optimization hint.

RIGHT JOIN

The mirror image of LEFT JOIN: every row from the right table, plus matching rows from the left. Native since 3.39.0.

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

In practice you rarely need RIGHT JOIN, because any right join can be rewritten as a left join by swapping the table order. Many style guides recommend always using LEFT JOIN for readability.

RIGHT JOIN on SQLite older than 3.39.0

Swap the tables and use a LEFT JOIN:

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

FULL OUTER JOIN

Returns all rows from both tables. Where a row has no match on the other side, that side's columns are NULL. Native since 3.39.0.

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

FULL OUTER JOIN on SQLite older than 3.39.0

Emulate it by combining a LEFT JOIN with a UNION ALL and a second LEFT JOIN that keeps only the unmatched right-side rows:

SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
 
UNION ALL
 
SELECT c.customer_id, c.name, o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

The WHERE c.customer_id IS NULL filter in the second half prevents the matched rows from appearing twice. Use UNION ALL rather than UNION so you don't pay for an unnecessary deduplication pass.

Self-joins

A self-join joins a table to itself, using table aliases to tell the two copies apart. Useful for hierarchies and for comparing rows within the same table.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

A LEFT JOIN here keeps the top-level employee whose manager_id is null, rather than dropping them.

Anti-joins (finding rows with no match)

There's no ANTI JOIN keyword. The standard pattern is a LEFT JOIN filtered to the null side:

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

This returns customers with no orders. The equivalent NOT EXISTS form is often clearer and lets SQLite stop scanning as soon as it finds one match:

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Avoid NOT IN for this when the subquery column can contain NULL. A single null in the list makes NOT IN return no rows at all, which is a classic silent-bug source.

How join order and the ON clause affect outer joins

With INNER JOIN, conditions in the ON clause and the WHERE clause are interchangeable. With outer joins they are not.

A condition in the ON clause is applied before the join decides which rows to keep. A condition in the WHERE clause is applied after, and can quietly turn a LEFT JOIN back into an inner join:

-- Keeps all customers; only joins orders over 100
SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id AND o.amount > 100;
 
-- Drops customers with no order, because o.amount is NULL for them
SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100;

If you want to filter the joined table but still keep unmatched left rows, the condition belongs in ON, not WHERE.

Indexing for joins

Joins perform best when the join columns are indexed. Foreign-key-style columns like orders.customer_id are prime candidates:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Use EXPLAIN QUERY PLAN to confirm SQLite is using an index (SEARCH ... USING INDEX) rather than a full table scan (SCAN). For more detail, see our guide on SQLite indexes.

Common mistakes

• Forgetting the ON clause on a non-CROSS join. SQLite will treat it as a cross join and silently return the Cartesian product.
• Filtering an outer join in WHERE instead of ON, which collapses it to an inner join (see above).
• Using NOT IN with a nullable column, which can return zero rows unexpectedly.
• Assuming RIGHT/FULL joins exist on every build. Check sqlite_version() if your code ships to environments you don't control, such as older mobile OS bundles.

When you're exploring an unfamiliar schema and aren't sure which join you need, Mako's AI autocomplete can suggest the join condition from your foreign keys so you can iterate quickly.

Mako connects to SQLite and eight other databases with AI-powered autocomplete. Try it free at mako.ai.