Why does a join create duplicates?

What does a line before and after join represent?

An order can contain several product lines. After join, a line therefore represents an ordered item and the order number is legitimately repeated.

The problem appears when we then add an amount stored at the order level: it is repeated once per line and becomes overvalued.

The query explained

Start by defining what a row of the result should represent. This decision determines the necessary columns, groupings, and controls.

The following example isolates the main mechanism. Run it, observe the result, then change one clause at a time to understand its effect.

SELECT p.categorie,
       SUM(l.quantite * l.prix_unitaire) AS chiffre_affaires
FROM lignes_commandes l
JOIN produits p ON p.id_produit = l.id_produit
GROUP BY p.categorie;

Read the result like an analyst

The measure uses the columns present at the row level of detail. The category comes from the product and does not change the quantity of detail lines.

To use an order metric, first aggregate the rows or subtable at the order level before joining.

• Count the lines before and after JOIN.
• Check the uniqueness of each key.
• Write the expected cardinality.
• Reconcile the final total with an independent source.

Errors that distort the analysis

A query can be valid without correctly answering the question. The most costly errors are often silent: wrong perimeter, multiplied line or missing value interpreted as zero.

Before sharing the result, compare it to a small hand-calculated sample and keep the scope definition with the query.

• Add DISTINCT without understanding the repetition.
• Sum an order amount after a join on the lines.
• Connect on a non-single column.
• Forget that a second detail table further multiplies the result.

Get into practice

Solve the three-table exercise and manually check an order with two lines.

The SQL simulator of SQL.tn only runs read queries on dummy data in your browser. You can try multiple writes and compare their results without installing any software.