LEFT JOIN, NULL and COALESCE
Identify missing or incomplete data.
Advanced
SQL syntax
Finance and accounting scenario
Related exercises
Objective
Keep unmatched rows and handle NULL values in calculations.
Exercise context
To analyze overdue balances, invoices must remain even when no payment exists. LEFT JOIN preserves those invoices.
Key concepts
- LEFT JOIN keeps every row from the left table.
- When no match exists, columns from the right table contain NULL.
- COALESCE replaces NULL with a chosen value, often 0.
- This logic is essential for calculating outstanding balances.
- Overdue balances are often calculated by comparing invoiced and paid amounts.
Syntax pattern
SELECT a.colonne, COALESCE(SUM(b.montant), 0) AS total
FROM table_a a
LEFT JOIN table_b b ON a.cle = b.cle
GROUP BY a.colonne;Applied example
SELECT f.id_facture,
f.montant_ttc - COALESCE(SUM(r.montant_regle), 0) AS reste_a_payer
FROM factures f
LEFT JOIN reglements r ON f.id_facture = r.id_facture
GROUP BY f.id_facture, f.montant_ttc;Common mistakes
- Using JOIN instead of LEFT JOIN to find unpaid items.
- Forgetting COALESCE and getting NULL in a calculation.
- Subtracting SUM from montant_ttc without GROUP BY.
- Filtering too early with WHERE instead of HAVING.