INNER JOIN to SQL: link customers and orders

Why is the data separated?

A relational database avoids copying the same information in each line. The clients table stores the name and city of each customer, while the commandes table stores purchases.

To display the customer name next to an order, SQL must combine these two sources when reading. This is the role of a join.

The common key makes the relationship possible

In clients, id_client uniquely identifies a person: this is the primary key. In commandes, the same value indicates who placed the order: it acts as a foreign key.

The relationship therefore does not depend on the name of the client. Two people can have the same name, while their identifier remains unique.

clients
- id_client  INTEGER  PRIMARY KEY
- nom        VARCHAR
- ville      VARCHAR

commandes
- id_commande  INTEGER  PRIMARY KEY
- id_client    INTEGER
- montant      DECIMAL

Write a INNER JOIN step by step

The query starts from clients, adds commandes, and then describes the match in the ON clause. INNER JOIN only keeps rows for which this match exists.

The aliases c and co shorten the references. They also avoid any ambiguity when both tables have a column with the same name.

• FROM clients AS c sets the first source.
• INNER JOIN commandes AS co adds the second table.
• ON c.id_client = co.id_client specifies which lines match.
• SELECT chooses only the columns useful for the result.

SELECT
  c.nom,
  co.id_commande,
  co.montant
FROM clients AS c
INNER JOIN commandes AS co
  ON c.id_client = co.id_client
ORDER BY co.montant DESC;

Read and check the result

An order associated with a customer produces a line in the result. If a customer has three orders, their name appears three times: these are not accidental duplicates, but three different facts.

Before adding calculations, check a few identifiers manually. A correct join must preserve the business meaning of the lines and not multiply their number without reason.

INNER JOIN or LEFT JOIN?

INNER JOIN answers the question "Which orders have a corresponding customer?" ". A customer without an order disappears from the result.

LEFT JOIN on the contrary keeps all the customers from the table on the left. Order columns are NULL when no order matches. This choice is useful for finding inactive customers.

SELECT c.nom, co.id_commande
FROM clients AS c
LEFT JOIN commandes AS co
  ON c.id_client = co.id_client;

Five common mistakes with joins

A query can run without technical errors but still produce an incorrect business result. The condition ON therefore deserves careful rereading.

• Forget the ON clause and create all possible combinations.
• Link the client's name instead of their stable ID.
• Confuse id_client and id_commande.
• Use INNER JOIN while unmatched rows should be preserved.
• Selecting all columns with * and making the result difficult to verify.

The method to remember

First identify the business question, then the table that represents each object. Then locate the primary key and foreign key, write the condition ON and check a few lines.

When this result is reliable, you can add a filter, sort or aggregation. The customers-orders exercise allows you to immediately apply this method.