SQLite INNER JOIN: Combine Rows From Multiple Tables

A Join Stitches Two Tables Together

Relational databases split data across tables on purpose — customers in one table, orders in another, products in a third. That keeps each fact in one place. But when you want to answer a real question ("which customers ordered what?"), you need to put the pieces back together. That's what a join does.

INNER JOIN is the workhorse. It pairs rows from two tables wherever a condition matches, and drops everything else.

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO orders (customer_id, total) VALUES (1, 49.99), (1, 12.50), (2, 80.00);

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id;

Three customers, three orders, but Chen has no orders — so Chen doesn't appear. That's the inner part: only matched rows survive.

The Mental Model: Match Rows, Then Filter

Read an INNER JOIN like this: take every row from the first table, look at every row from the second table, and keep the pair only when the ON condition is true. Conceptually it's a giant cross-product followed by a filter. SQLite doesn't actually do it that way (it uses indexes when it can), but the model is correct for predicting what comes out.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 1, 70), (12, 2, 15);

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

A few habits worth picking up here:

• Alias tables (customers AS c) when you'll mention them more than once. It cuts noise.
• Qualify columns (c.name, o.total) when both tables could plausibly own them.
• The order in ON o.customer_id = c.id doesn't matter — c.id = o.customer_id works the same.

INNER JOIN vs JOIN

In SQLite (and standard SQL), JOIN on its own means INNER JOIN. The INNER keyword is optional.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Both styles produce the same plan and the same rows. Spelling out INNER JOIN is a tiny readability win in code that mixes join types — it makes the intent obvious next to a LEFT JOIN a few lines down.

ON vs USING

When the join columns share a name in both tables, USING (column) is shorter than ON a.col = b.col:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT name, total
FROM customers
INNER JOIN orders USING (customer_id);

USING (customer_id) does two things: matches on equal customer_id, and merges the column so it appears once in the result. Reach for it when both sides really do use the same name. Stick with ON when names differ (orders.customer_id = customers.id) or the condition is more than a single equality.

Joining Three Tables

Chain joins by adding more JOIN ... ON ... clauses. Each one links the running result to another table.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
CREATE TABLE order_items (
    order_id INTEGER,
    product TEXT,
    quantity INTEGER
);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);
INSERT INTO order_items VALUES (10, 'Notebook', 2), (10, 'Pen', 5), (11, 'Mug', 1);

SELECT c.name, o.id AS order_id, i.product, i.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items i ON i.order_id = o.id
ORDER BY c.name, o.id;

Read it top-to-bottom: customers connect to orders, orders connect to items. Each row in the output represents one customer–order–item combination. Anything missing a match anywhere along the chain is dropped — that's the inner-join rule applied at every step.

Filtering With WHERE

ON says how to pair rows. WHERE filters the paired result. For inner joins specifically, putting an extra condition in ON versus WHERE produces the same rows — but the convention is to keep join conditions in ON and row filters in WHERE.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada', 'UK'), (2, 'Boris', 'FR'), (3, 'Chen', 'UK');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 80), (12, 3, 12);

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'UK' AND o.total > 20;

That reads as "join customers and orders, then keep only UK customers whose order is over 20." Two roles, two clauses — your future self will thank you. (Once you start writing LEFT JOINs, the ON/WHERE distinction stops being cosmetic, but that's the next page.)

Multiple Conditions in ON

ON can hold any boolean expression, not just one equality. Useful when the relationship spans more than one column, or when you want to filter the right side at join time.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, status TEXT);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES
    (10, 1, 30, 'paid'),
    (11, 1, 50, 'cancelled'),
    (12, 2, 15, 'paid');

SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o
    ON o.customer_id = c.id
   AND o.status = 'paid';

The cancelled order disappears because the second condition fails. For an inner join you could equally write WHERE o.status = 'paid' and get the same result. The ON version keeps the "what counts as a match" logic close to the join.

Common Pitfalls

A few things that trip people up:

• Forgetting the ON clause. FROM a INNER JOIN b with no ON is a syntax error in SQLite. (A bare comma — FROM a, b — does compile, gives you a cross join, and is almost never what you wanted.)
• Duplicates you didn't expect. If a customer has three orders, the customer's name appears three times in the result. That's correct join behavior, not a bug. Aggregate with GROUP BY if you want one row per customer.
• Missing rows. If a customer should have appeared but didn't, the join condition didn't match — check for NULLs on the join columns, or reach for LEFT JOIN.
• Ambiguous column names. SELECT id FROM customers JOIN orders ON ... errors out because both tables have an id. Qualify it: c.id or o.id.

Next: LEFT JOIN

INNER JOIN is great when missing matches mean "skip this row." But sometimes you want every customer listed, even the ones with no orders, with NULLs standing in for the missing data. That's LEFT JOIN, coming up next.