What population should you keep?
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.
Test the difference on concrete data
The shop wants to see every customer, including those without orders. Emma has two orders, James one and Olivia none. Which join will keep Olivia?
| customer_id | customer_name |
|---|---|
| 1 | Emma |
| 2 | James |
| 3 | Olivia |
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
| 103 | 2 |
SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;Run LEFT JOIN, then replace it with INNER JOIN. Olivia disappears. Emma’s two rows are not accidental duplicates: they represent two different orders.
Check your result: four rows, including Olivia with a NULL order number. A later WHERE filter on an orders column can exclude the customer without an order.
Browser-based SQL environment
Test the difference on concrete data
Run a read-only query on this exercise's fictional data. Nothing is sent to SQL.tn.