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

A join in SQLite pairs each row of one table with the matching rows of another table, and returns each pair as one row. For example, you can list each order with the name of the customer who placed it.

SQLite runs INNER, LEFT and CROSS JOIN in every version. RIGHT JOIN and FULL OUTER JOIN need SQLite 3.39.0 or later:

Every join has the same shape:

SELECT columns
FROM first_table
[INNER | LEFT | RIGHT | FULL OUTER | CROSS] JOIN second_table
  ON join_condition;

• first_table is the left table, and second_table is the right table.
• The join type decides which rows without a match stay in the result. JOIN alone is an INNER JOIN.
• ON join_condition pairs the rows, usually by comparing a key column of each table. CROSS JOIN needs no condition.

Check the version from the program that runs your queries, such as your Python script or your app. The sqlite3 shell on the same machine can be a different SQLite version.

SELECT sqlite_version();

The queries in this article ran on SQLite 3.50.4.

Create two sample tables, with four customers and four orders:

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');

Two rows have no match. Dina has no order, and order 104 points at customer 99, but no customer has that id. The join types differ in what they do with Dina and order 104:

INNER JOIN

INNER JOIN returns only the pairs of rows that match the ON condition. For example, you can list the orders whose customer exists, with the customer's name.

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;

Orders 101 to 103 each matched one customer. Order 104 drops out, because no customer has the id 99. Dina drops out too, because no order points at her.

LEFT JOIN

LEFT JOIN returns every row of the left table, with the matching row of the right table beside it. For example, you can list every customer with their order, including the customers who have ordered nothing.

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 is back, with NULL in the two columns from orders. Order 104 is still missing, because orders is the right table.

RIGHT JOIN

RIGHT JOIN returns every row of the right table, with the matching row of the left table beside it. For example, you can list every order with its customer's name, including an order whose customer is missing.

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;

Order 104 is in the result, with NULL as its name. Dina drops out, because customers is the left table.

RIGHT JOIN arrived in SQLite 3.39.0, according to the SQLite change log. For an older library, see SQLite older than 3.39.0 below.

FULL OUTER JOIN

FULL OUTER JOIN returns the matching pairs, plus the rows without a match from both tables. For example, you can find the customers without an order and the orders without a customer in one query.

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;

The result holds the three matching pairs, plus Dina and order 104. FULL JOIN without OUTER is the same join.

In an ORDER BY, SQLite sorts NULL before every other value. Dina comes first, because her order_id is NULL.

CROSS JOIN

CROSS JOIN pairs every row of the left table with every row of the right table. For example, you can count the pairs that four customers and four orders make.

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

Four customers times four orders make 16 pairs. In SQLite, CROSS JOIN also keeps the tables in the order you wrote them, which can speed up a slow join.

USING

USING joins two tables on a column that has the same name in both. For example, you can join orders and customers on customer_id without writing the ON condition.

SELECT *
FROM orders
JOIN customers USING (customer_id)
ORDER BY order_id;

The result has one customer_id column. The same SELECT * with ON returns two, one from each table. Where the column names differ, use ON.

What goes wrong

SQLite older than 3.39.0

A SQLite library older than 3.39.0 rejects RIGHT JOIN and FULL OUTER JOIN with an error. A query copied from a PostgreSQL project hits this, because PostgreSQL has both joins.

Rewrite a RIGHT JOIN as a LEFT JOIN with the two tables swapped. Rewrite a FULL OUTER JOIN as a LEFT JOIN plus the unmatched rows of the right table, combined with UNION ALL:

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 query returns the same five rows as the FULL OUTER JOIN above. The second SELECT returns only order 104, so no row comes back twice. UNION would return the same rows after a duplicate check, which UNION ALL skips.

A WHERE condition on the right table of a LEFT JOIN

A condition in WHERE on a column of the right table removes the customers that LEFT JOIN kept without a match. For example, this query is meant to list every customer, with each of their orders that is not Apples:

SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.product <> 'Apples'
ORDER BY c.customer_id;

Tom and Dina are gone. WHERE drops Tom's row, because his only order is Apples. Dina has no order, so her product is NULL, and NULL <> 'Apples' is not true.

The SQLite query optimizer runs such a LEFT JOIN as an inner join.

Move the condition into ON:

SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
   AND o.product <> 'Apples'
ORDER BY c.customer_id;

Every customer is back. Tom's Apples order no longer matches, so Tom gets NULL, like Dina.

A slow join

SQLite runs a join as nested loops. An outer loop reads the rows of one table, and for each of them an inner loop looks up the matching rows of the other table.

EXPLAIN QUERY PLAN lists the tables from the outer loop to the inner loop, and shows how SQLite finds the rows:

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

SCAN c reads every customer in the outer loop. To find the orders, SQLite builds an automatic index that lasts only for this one statement. SQLite builds that index again every time the statement runs.

Create the index once:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

The same EXPLAIN QUERY PLAN now names that index:

Declaring orders.customer_id as a foreign key would not create this index. The SQLite foreign key documentation recommends an index on the referencing column of every foreign key.

SQLite can also read the tables of a JOIN in another order than you wrote them. For FROM customers c JOIN orders o on the sample tables, it scans orders in the outer loop. Write CROSS JOIN in place of JOIN, keep the ON condition, and customers moves to the outer loop.

SQLite "chooses to never reorder tables in a CROSS JOIN". Use CROSS JOIN this way only where you have measured that the order you wrote runs faster.

Run the joins in DbSchema

The SQL Editor in DbSchema runs a query against a SQLite file and shows its rows in a grid under the editor. Press Editor in the toolbar, choose SQL Editor, paste a query from this article, and press Run.

The Query Builder in DbSchema writes the join from a foreign key. The sample orders table has no foreign key, so start with a virtual foreign key. DbSchema keeps a virtual foreign key in its model and never writes it to the SQLite file.

1. Drag orders.customer_id onto customers.customer_id in the diagram, tick Virtual in the dialog, and press OK.
2. Right-click orders on the diagram and choose Query Editor.
3. Click the icon at the right of customer_id, and choose the entry that joins customers.
4. Click the join label on the line between the tables, and choose Left Join or Full Join.

Download DbSchema at https://dbschema.com/download.html, connect it to your SQLite file, and run each join of this article in the SQL Editor. The free Community Edition includes the connection, the diagram and the SQL Editor. The Query Builder needs DbSchema Pro, and so does saving the model file that keeps a virtual foreign key.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download