SQL INNER JOIN: link two tables

In a well-designed database, information is spread over several tables: orders on one side, customers on the other. To show “order no. 12 was placed by Léa Martin”, you need to link these tables. That is what a join does, and INNER JOIN (or just JOIN) is the most common one.

Syntax

SELECT a.column, b.column
FROM table_a a
JOIN table_b b ON b.key = a.foreign_key;

How it works

Primary key and foreign key

Each customer has a unique identifier, its primary key: clients.id. In the commandes (orders) table, the client_id column holds the identifier of the customer who placed the order: it is a foreign key. The join pairs the rows where commandes.client_id = clients.id.

How INNER JOIN works

For each row of the first table, SQL looks for the rows of the second one that meet the ON condition, and builds one result row per match. A row without a match disappears from the result: that is the difference with LEFT JOIN.

Table aliases

FROM commandes o JOIN clients c gives the tables the nicknames o and c. You then write c.nom or o.id: it is shorter, and essential when two tables have a column with the same name.

Chaining joins

You can add as many JOINs as needed. For example: order lines → products → suppliers.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT o.id AS commande, o.date_commande, c.prenom, c.nom
FROM commandes o
JOIN clients c ON c.id = o.client_id
LIMIT 6;

SELECT o.id, c.prenom, c.nom, o.date_commande
FROM commandes o
JOIN clients c ON c.id = o.client_id
WHERE o.statut = 'annulée' AND c.ville = 'Lyon';

SELECT p.categorie, ROUND(SUM(l.quantite * l.prix_unitaire), 2) AS ca
FROM lignes_commande l
JOIN produits p ON p.id = l.produit_id
GROUP BY p.categorie
ORDER BY ca DESC;

SELECT p.nom AS produit, f.nom AS fournisseur, f.pays
FROM produits p
JOIN fournisseurs f ON f.id = p.fournisseur_id
ORDER BY f.pays
LIMIT 8;

Common mistakes

SELECT * FROM commandes o JOIN clients c;

SELECT * FROM commandes o JOIN clients c ON c.id = o.client_id;

SELECT * FROM commandes o JOIN clients c ON c.id = o.id;

SELECT * FROM commandes o JOIN clients c ON c.id = o.client_id;

SELECT id, nom FROM commandes o JOIN clients c ON c.id = o.client_id;

SELECT o.id, c.nom FROM commandes o JOIN clients c ON c.id = o.client_id;

Exercises

Try writing the query yourself before opening the solution.

SELECT l.commande_id, p.nom, l.quantite FROM lignes_commande l JOIN produits p ON p.id = l.produit_id LIMIT 10;

SELECT e.prenom, e.nom, COUNT(*) AS nb FROM commandes o JOIN employes e ON e.id = o.employe_id GROUP BY e.id ORDER BY nb DESC;

Frequently asked questions