Linking Two Tables with INNER JOIN

In a real database, information is not all stored in a single table. A store keeps its customers in one table and its orders in another. Why? So that a customer’s name and city are not copied into each of their orders: if the customer moves, only one row has to be changed.

The link between the two tables is made with keys:

clients.id is the primary key: it uniquely identifies each customer;
commandes.client_id is a foreign key: it holds the id of the customer who placed the order.

clients                  commandes
id | nom     | ville     id  | client_id | statut
1  | Amina   | Lyon      101 | 1         | payee
2  | Bastien | Paris     103 | 2         | annulee

The inner join

To display each order with the customer’s name, you join the two tables:

SELECT commandes.id, commandes.date_commande, clients.nom
FROM commandes
INNER JOIN clients
        ON clients.id = commandes.client_id;   -- matching condition

For each order, the database looks for the customer(s) whose id equals client_id, and builds a combined row. INNER is optional: JOIN alone means an inner join.

An inner join keeps only the rows that have a match on both sides. A customer with no orders (Emma) therefore does not appear in the result. The next lesson shows how to keep her.

Table aliases

Writing out the full table names quickly becomes tedious. You give them a short alias:

SELECT co.id, co.date_commande, cl.nom, cl.ville
FROM commandes AS co
JOIN clients   AS cl ON cl.id = co.client_id
WHERE co.statut = 'payee'
ORDER BY co.date_commande;

Both tables have an id column. Writing id alone would be ambiguous and cause an error, so you specify co.id or cl.id. A good habit is to prefix all columns as soon as there is a join.

Joins multiply rows

A join does not return “one row per source table” but one row per match. Amina has two orders: she appears twice. This is normal, and it is what later lets you count or sum.

Join, then aggregate

The result of a join can be handled like a table: you can filter it, group it and sort it.

-- Number of orders per customer city
SELECT cl.ville, COUNT(*) AS nb_commandes
FROM commandes AS co
JOIN clients   AS cl ON cl.id = co.client_id
GROUP BY cl.ville
ORDER BY nb_commandes DESC;

Method

Identify the tables that contain the information you want.
Spot the foreign key that links them.
Write the JOIN ... ON and check the number of rows you get.
Only then add filters, grouping and sorting.

Getting into the habit of checking the row count after each join prevents most calculation errors.

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

CodeDifficulty 1SQL (SQLite) in the browser2 hints

Orders and customer name

Available tables: clients(id, nom, ville), commandes(id, client_id, date_commande, statut), produits(id, nom, categorie, prix), lignes_commande(commande_id, produit_id, quantite), fournisseurs(id, nom, ville).

Display the id of each order and the name of the customer who placed it, sorted by order id.

Exercise data (SQL)

CREATE TABLE clients (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  ville TEXT NOT NULL
);
CREATE TABLE commandes (
  id INTEGER PRIMARY KEY,
  client_id INTEGER NOT NULL REFERENCES clients(id),
  date_commande TEXT NOT NULL,
  statut TEXT NOT NULL            -- 'payee' ou 'annulee'
);
CREATE TABLE produits (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  categorie TEXT NOT NULL,
  prix INTEGER NOT NULL
);
CREATE TABLE lignes_commande (
  commande_id INTEGER NOT NULL REFERENCES commandes(id),
  produit_id INTEGER NOT NULL REFERENCES produits(id),
  quantite INTEGER NOT NULL,
  PRIMARY KEY (commande_id, produit_id)
);
CREATE TABLE fournisseurs (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  ville TEXT NOT NULL
);
INSERT INTO clients VALUES
 (1, 'Amina', 'Lyon'), (2, 'Bastien', 'Paris'), (3, 'Camille', 'Lille'),
 (4, 'David', 'Paris'), (5, 'Emma', 'Nantes');
INSERT INTO commandes VALUES
 (101, 1, '2026-01-10', 'payee'), (102, 1, '2026-02-03', 'payee'),
 (103, 2, '2026-02-15', 'annulee'), (104, 3, '2026-03-01', 'payee'),
 (105, 4, '2026-03-05', 'payee'), (106, 2, '2026-03-20', 'payee');
INSERT INTO produits VALUES
 (1, 'Clavier', 'Informatique', 50), (2, 'Souris', 'Informatique', 20),
 (3, 'Lampe', 'Maison', 35), (4, 'Chaise', 'Mobilier', 189), (5, 'Plante', 'Maison', 15);
INSERT INTO lignes_commande VALUES
 (101, 1, 1), (101, 2, 2), (102, 3, 1), (103, 4, 1),
 (104, 2, 3), (104, 3, 2), (105, 4, 2), (106, 1, 1), (106, 2, 1);
INSERT INTO fournisseurs VALUES
 (1, 'Atelier Bois', 'Nantes'), (2, 'TechPro', 'Lyon'), (3, 'Verdure', 'Bordeaux');

SELECT co.id, cl.nom
FROM commandes AS co
JOIN ...
ORDER BY co.id;

CodeDifficulty 2SQL (SQLite) in the browser2 hints

Orders by city

Display each customer city and the number of orders (all statuses, alias nb_commandes) placed by customers in that city. Sort by descending count.

Exercise data (SQL)

CREATE TABLE clients (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  ville TEXT NOT NULL
);
CREATE TABLE commandes (
  id INTEGER PRIMARY KEY,
  client_id INTEGER NOT NULL REFERENCES clients(id),
  date_commande TEXT NOT NULL,
  statut TEXT NOT NULL            -- 'payee' ou 'annulee'
);
CREATE TABLE produits (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  categorie TEXT NOT NULL,
  prix INTEGER NOT NULL
);
CREATE TABLE lignes_commande (
  commande_id INTEGER NOT NULL REFERENCES commandes(id),
  produit_id INTEGER NOT NULL REFERENCES produits(id),
  quantite INTEGER NOT NULL,
  PRIMARY KEY (commande_id, produit_id)
);
CREATE TABLE fournisseurs (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  ville TEXT NOT NULL
);
INSERT INTO clients VALUES
 (1, 'Amina', 'Lyon'), (2, 'Bastien', 'Paris'), (3, 'Camille', 'Lille'),
 (4, 'David', 'Paris'), (5, 'Emma', 'Nantes');
INSERT INTO commandes VALUES
 (101, 1, '2026-01-10', 'payee'), (102, 1, '2026-02-03', 'payee'),
 (103, 2, '2026-02-15', 'annulee'), (104, 3, '2026-03-01', 'payee'),
 (105, 4, '2026-03-05', 'payee'), (106, 2, '2026-03-20', 'payee');
INSERT INTO produits VALUES
 (1, 'Clavier', 'Informatique', 50), (2, 'Souris', 'Informatique', 20),
 (3, 'Lampe', 'Maison', 35), (4, 'Chaise', 'Mobilier', 189), (5, 'Plante', 'Maison', 15);
INSERT INTO lignes_commande VALUES
 (101, 1, 1), (101, 2, 2), (102, 3, 1), (103, 4, 1),
 (104, 2, 3), (104, 3, 2), (105, 4, 2), (106, 1, 1), (106, 2, 1);
INSERT INTO fournisseurs VALUES
 (1, 'Atelier Bois', 'Nantes'), (2, 'TechPro', 'Lyon'), (3, 'Verdure', 'Bordeaux');

SELECT cl.ville, COUNT(*) AS nb_commandes
FROM ...
GROUP BY ...
ORDER BY ...;

Multiple choiceDifficulty 11 hint
Ambiguous column
The query SELECT id, nom FROM commandes JOIN clients ON clients.id = commandes.client_id; fails. Why?
- The ON condition is reversed
- The id column exists in both tables and is not qualified
- JOIN must always be written INNER JOIN

End-of-lesson quiz

What does commandes.client_id contain?
- The customer's name
- The identifier (primary key) of the customer who placed the order
- The order number
Does a customer with no orders appear in an INNER JOIN between clients and commandes?
- Yes, with NULLs
- No
- Yes, once
What happens if you forget the join condition (FROM a, b with no WHERE)?
- An error
- A Cartesian product: every combination of rows
- Only identical rows are kept

The rest of the course

Keeping All Rows: LEFT JOIN and the Anti-Join18 min
Multiple Joins and Self-Joins18 min
Subqueries: Scalar, IN, EXISTS and Correlated20 min
Conditional Logic and Set Operations: CASE, UNION, EXCEPT18 min

Build my path See the full syllabus

Intermediate SQL (joins, subqueries) · Intermediate · 2 h 32 min

Objectives