INNER JOIN or LEFT JOIN: which one to choose?

What population should you maintain?

INNER JOIN returns only rows with a match. LEFT JOIN keeps all the rows from the left table and completes the missing columns with NULL.

To find customers without an order, customers must be left. To analyze only orders linked to a valid customer, INNER JOIN better expresses the need.

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 c.nom, COUNT(co.id_commande) AS commandes
FROM clients c
LEFT JOIN commandes co ON co.id_client = c.id_client
GROUP BY c.id_client, c.nom;

Read the result like an analyst

COUNT is for the nullable command ID, not *. An unmatched customer thus gets zero.

A filter on orders placed in WHERE could remove the NULL lines and cancel the effect of LEFT JOIN; place it in ON if the left population must remain complete.

• Name the reference population.
• Plan for unmatched lines.
• Count one column on the right side.
• Check the filters placed in ON or WHERE.

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.

• Choose INNER JOIN by habit.
• Place a right filter in WHERE after LEFT JOIN.
• Count * and display 1 for an absence.
• Group only by name and merge homonyms.

Get into practice

Do the customers exercise without an order and then rewrite it with NOT EXISTS.

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.