INNER JOIN: connecting two tables

Cross-reference data from two tables with INNER JOIN, readable aliases and a precise join condition.

7 min · 3 questions

A real database never stores everything in a single table: customers on one side, their orders on the other. To practise this module, open sqliteonline.com in your browser (no installation needed) or launch the sqlite3 tool in a terminal. First paste the script below: it creates the two tables used in this lesson.

SQL

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

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount REAL NOT NULL,
  ordered_at TEXT NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Alice', 'Lyon'),
  (2, 'Karim', 'Paris'),
  (3, 'Mina', 'Nantes');

INSERT INTO orders
  (id, customer_id, amount, ordered_at)
VALUES
  (1, 1, 49.90, '2026-06-01'),
  (2, 1, 15.00, '2026-06-12'),
  (3, 2, 120.50, '2026-06-15');

Starting script: run it before the examples that follow.

INNER JOIN assembles two tables row by row according to a matching condition written after ON. Here, each order is linked to the customer whose id equals its customer_id. Rows without a match are dropped from the result: Mina, who ordered nothing, does not appear.

SQL

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

Each order together with the name of its customer.

Repeating the full table names quickly becomes unreadable. An alias declared with AS renames the table for the duration of the query. Always prefix ambiguous columns: id exists in both tables, so write c.id or o.id, never id on its own.

SQL

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

Short aliases, a filter and a sort: a typical professional query.

Choose aliases that are short but meaningful: c for customers, o for orders. In a four-table query, consistent aliases make all the difference when you re-read it.

In SQLite, JOIN on its own is equivalent to INNER JOIN, and a forgotten ON clause produces a cartesian product: every customer paired with every order. Always check that your condition genuinely links the primary key of one table to the foreign key of the other.

Knowledge check

Make sure you remember the key points of this lesson.

In a join, which keyword introduces the matching condition between the two tables?
- WHERE
- ON
- HAVING
- MATCH
With an INNER JOIN between customers and orders, what happens to a customer who has no orders?
- They appear with NULL columns
- They are absent from the result
- The query fails
Why write c.id rather than id in a query that joins customers and orders?
- To speed up the execution of the query
- Because id exists in both tables and would be ambiguous
- Because SQLite requires it for every column