SQLite JOINs: INNER, LEFT, CROSS, and FULL JOIN Workarounds

A join you copied from a PostgreSQL project runs unchanged against SQLite most of the time, and then one day it does not: RIGHT JOIN arrived only in SQLite 3.39.0, and the copy of SQLite compiled into your phone app or your Python build may be older than that. Three other things are SQLite's own. Joins run as nested loops, CROSS JOIN is the one keyword that fixes the loop order, and a foreign key gives you no index for free.

Run SELECT sqlite_version(); to see which library you have. Below 3.39.0, write a RIGHT JOIN as a LEFT JOIN with the two tables swapped, and a FULL OUTER JOIN as two LEFT JOINs combined with UNION ALL.

What SQLite JOINs do

A join reads two tables in one query and pairs the rows whose values match, and the join type decides what happens to a row that finds no partner. That much is the same in every SQL engine, and SQL Joins Explained walks through each type with the rows it keeps and the rows it drops. The rest of this article is about SQLite specifically: how it executes a join, and what to do when one gets slow.

The schema behind a join matters as much as the join does, so SQLite CREATE TABLE and SQLite Constraints are worth reading alongside this one.

Sample schema used in this guide

Two tables, small enough that every join type below returns something visibly different:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL
);

CREATE TABLE orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER,
    product      TEXT NOT NULL
);

INSERT INTO customers VALUES
    (1, 'Bob'),
    (2, 'Alice'),
    (3, 'Tom'),
    (4, 'Dina');

INSERT INTO orders VALUES
    (101, 3, 'Apples'),
    (102, 1, 'Bananas'),
    (103, 2, 'Grapes'),
    (104, 99, 'Oranges');

One row on each side has no partner on the other:

• Dina has ordered nothing
• order 104 points at customer 99, who does not exist

Note also that orders declares no foreign key, since nothing in the statement says REFERENCES, and that comes back twice below: once when the queries get slow, and once in DbSchema.

INNER JOIN

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

Order 104 is gone, because customer 99 is not in the customers table, and Dina is absent because she has no order. Writing JOIN on its own means the same thing here, and so does listing both tables separated by a comma with the condition in the WHERE clause. The ORDER BY is doing real work: without it SQLite is free to return these three rows in whatever order the plan produces.

LEFT JOIN

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;

Dina keeps her row and the columns from orders come back empty, which is what a report wants when it has to list every customer, ordered or not. The side you write first is the side that survives, and SQLite takes that literally: the query optimizer overview says inner joins "can be freely reordered", while "outer joins are neither commutative nor associative and hence will not be reordered".

CROSS JOIN

CROSS JOIN pairs every row on the left with every row on the right and has no condition at all, so four customers and four orders make sixteen rows:

SELECT count(*) AS pairs
FROM customers c
CROSS JOIN orders o;

In SQLite the keyword carries a second meaning that has nothing to do with the result. SQLite runs joins as nested loops, and it normally picks which table goes in the outer loop, "in a different order if doing so will help it to select better indexes". CROSS JOIN switches that off: "SQLite chooses to never reorder tables in a CROSS JOIN. Hence, the left table of a CROSS JOIN will always be in an outer loop relative to the right table." When a query is slow because SQLite picked the wrong table to drive the loop, writing CROSS JOIN between the tables is how you overrule it, and the documentation is explicit that only that keyword does it: "INNER JOIN, NATURAL JOIN, JOIN, and other similar combinations work just like a comma join in that the optimizer is free to reorder tables as it sees fit."

RIGHT JOIN and FULL OUTER JOIN in SQLite

Both arrived in the same release. The SQLite change log records version 3.39.0, dated 2022-06-25, as adding "(long overdue) support for RIGHT and FULL OUTER JOIN". Ask the library in front of you before you rely on them, because the version that matters is the one compiled into your application, not the one in your terminal:

SELECT sqlite_version();

RIGHT JOIN

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

Every order survives, including 104, whose customer column points at nobody. Dina is not here, because she is on the left.

FULL OUTER JOIN

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

Five rows: the three matches, the customer with no order, and the order with no customer. Dina sorts to the top because her order_id is empty, and in SQLite "values with storage class NULL come first" in an ORDER BY.

When the version is older than 3.39.0

For a full outer join, take every row from the left, then add the right-side rows that matched nothing:

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

UNION ALL

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

ORDER BY order_id;

The result is the same five rows as the FULL OUTER JOIN above. UNION ALL rather than UNION is deliberate, because the second query returns only rows the first one could not produce, and UNION would pay for a duplicate check that has nothing to remove. SQLite UNION covers the difference.

SQLite vs PostgreSQL join comparison

The syntax is the same, so a join written for one engine reads correctly in the other. PostgreSQL accepts all five join types, which makes the 3.39.0 boundary SQLite's alone: a RIGHT JOIN or FULL OUTER JOIN taken from a PostgreSQL project is what fails against an older SQLite library.

The difference that outlives the version check is how the join is executed. SQLite "implements joins as nested loops", one loop per table, and picks indexes to make the inner loops cheap. If you write the same queries against both engines, PostgreSQL JOINs covers that side.

Join performance and index guidance

A slow join in SQLite is almost always an inner loop with no index to use, so it scans the whole table once for every row of the outer loop. The join column on the child side is what wants the index:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

A foreign key would not have given you this. The foreign key documentation recommends it separately: "in most real systems, an index should be created on the child key columns of each foreign key constraint", because the constraint itself only checks values.

EXPLAIN QUERY PLAN tells you whether the index is being used:

EXPLAIN QUERY PLAN
SELECT o.order_id, c.name
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id;

The plan prints one line per table. SCAN means SQLite is reading every row of that table, and SEARCH means "that only a subset of the table rows are visited", with the index it used named on the same line. A SCAN on the inner table of a two-table join is the line to fix. SQLite EXPLAIN PLAN reads a plan line by line, and SQLite Indexes covers which columns to index.

Two smaller habits keep joins cheap:

• select the columns you need instead of SELECT *, because every extra column is read and copied for every row the loop produces
• put the conditions that throw rows away in the query rather than in the application, so they cut the loop down before the join runs rather than after

Run JOINs in sqlite3, Python, and DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

sqlite3 CLI

The shell prints results as bare pipe-separated values until you tell it otherwise:

sqlite3 sales.db

sqlite> .mode table
sqlite> .headers on

.mode table draws the result with borders and .headers on puts the column names above it, which is the difference between reading a five-column join and counting fields by hand.

Python

import sqlite3

with sqlite3.connect("sales.db") as connection:
    rows = connection.execute("""
        SELECT o.order_id, c.name, o.product
        FROM orders o
        LEFT JOIN customers c
            ON o.customer_id = c.customer_id
        WHERE o.product <> ?
        ORDER BY o.order_id
    """, ("Oranges",)).fetchall()

The ? placeholder is how the value reaches SQLite as a value rather than as text pasted into the SQL.

DbSchema

DbSchema builds joins from the relationships it can see, which is why the missing foreign key in the sample schema matters. Drag orders.customer_id onto customers.customer_id in the diagram and DbSchema records a virtual foreign key: a line on the diagram, saved in the .dbs model file, with nothing written to the SQLite database. The constraint is still absent from the file, and DbSchema now treats the relationship as real everywhere it builds queries.

Building the join itself is a sequence of clicks:

1. Click a table header in the diagram, and DbSchema opens the Query Builder loaded with that table.
2. Click the small arrow next to a column to follow the foreign key, real or virtual, and DbSchema adds the other table and the join between them.
3. Click the join type label on the connecting line to switch the join between INNER JOIN, LEFT JOIN and EXISTS.
4. Tick the columns you want in the result.

DbSchema writes the SQL at the bottom of the builder and rewrites it every time you click.

To follow the same relationships through the data instead of through a query, open the Relational Data Editor on a table: it opens the related tables as panes beside it, and clicking a row in the parent refilters every child pane to the rows that match, as many levels deep as the relationships go. Queries you would rather type go into the SQL Editor and run against the connected file when you press Execute Query.

Check sqlite_version() before you write RIGHT or FULL, index the column on the child side of every join you run often, and reach for CROSS JOIN when the plan picks the wrong table to drive the loops. To see the relationships behind your joins as a diagram, download DbSchema at https://dbschema.com/download.html and connect it to your SQLite file: the diagram, virtual foreign keys and the SQL Editor are in the free Community Edition, and the visual Query Builder and the Relational Data Editor are in Pro.

FAQ

Should I use ON or USING?

USING(column_name) works only when the column has the same name in both tables, and it writes the join condition for you. ON takes any condition, which you need when the names differ or when the join is not a plain equality.

Does a foreign key make a join faster?

No, and the index a join wants is the one you add yourself on the child key column. The parent side is where SQLite insists instead: the foreign key documentation requires the parent key to be the primary key of its table, or to carry a UNIQUE index in the collation that table's CREATE TABLE declared. A parent key that fails that test raises "foreign key mismatch" the first time a statement writes to either table, not when you create the child.

Sources

1. SQLite documentation: Query Planner Overview
2. SQLite documentation: Release History
3. SQLite documentation: EXPLAIN QUERY PLAN
4. SQLite documentation: SQLite Foreign Key Support
5. SQLite documentation: Datatypes In SQLite