NULL and COALESCE in SQL: handle missing values

What does NULL mean in SQL?

NULL reports the absence of a known value. It does not represent zero, an empty string, or the text “NULL”. A delivery date NULL can, for example, mean that the order has not yet been delivered.

This nuance is essential: automatically replacing an absence with zero can transform unknown information into an incorrect statement. Before any query, document why the column accepts NULL.

Search for a missing value with IS NULL

The telephone = NULL condition does not return the expected rows. A comparison with an unknown value produces an unknown result, which WHERE does not preserve.

SQL therefore provides the operators IS NULL and IS NOT NULL. The first searches for absences; the second keeps the values ​​entered.

• Correct: telephone IS NULL.
• Correct: telephone IS NOT NULL.
• Incorrect: telephone = NULL or telephone <> NULL.

SELECT id_client, nom
FROM clients
WHERE telephone IS NULL
ORDER BY id_client ASC;

Why does NULL change conditions?

A SQL condition can be true, false, or unknown. If remise is worth NULL, the expressions remise = 0, remise > 0 and remise <> 0 are unknown: none allows the real amount to be deduced.

This logic explains why a negative condition does not necessarily retrieve all other rows. When the rule must include absences, write it explicitly with OR remise IS NULL.

SELECT produit, remise
FROM produits
WHERE remise = 0
   OR remise IS NULL;

COALESCE returns the first available value

COALESCE reads its arguments from left to right and returns the first one that is not NULL. This function is useful for displaying an alternative label or choosing between several contact methods.

It does not modify the recorded data. The replacement only exists in the query result, which preserves the original information.

SELECT
  nom,
  COALESCE(telephone, email, 'Not provided') AS contact
FROM clients
ORDER BY nom ASC;

Replace NULL in a calculation only if the rule allows it

A quantity NULL generally makes an arithmetic calculation unknown. If the business model establishes that a missing discount is indeed equal to zero, COALESCE can make this rule explicit.

On the other hand, unknown income should not be arbitrarily transformed into zero income. This decision would artificially lower an average and could distort an indicator.

• Set the direction of NULL before calculation.
• Use a replacement value of the same type.
• Keep the absence if it contains useful information.

SELECT
  produit,
  prix - COALESCE(remise, 0) AS prix_net
FROM produits;

COUNT, SUM and AVG do not all treat NULL the same

COUNT(*) counts all rows, while COUNT(colonne) ignores NULL values in this column. This difference makes it easy to measure the total number of customers and the number of telephones entered.

SUM and AVG also ignore the NULL values. Using AVG(COALESCE(note, 0)) on the contrary adds the absences to the denominator with a zero value: the result therefore no longer has the same meaning.

SELECT
  COUNT(*) AS nombre_clients,
  COUNT(telephone) AS telephones_renseignes,
  ROUND(100.0 * COUNT(telephone) / NULLIF(COUNT(*), 0), 1) AS taux_renseignement
FROM clients;

Common mistakes to avoid

The processing of NULL must be visible in the query and justified by the business need, not added just to remove empty boxes.

• Compare a column at NULL with = or <>.
• Confusing NULL, zero and empty string.
• Use COALESCE without checking type compatibility.
• Replace absences with zero before AVG without justification.
• Forgetting that a concatenation or operation can become NULL depending on the SQL engine.

A reliable method in four questions

First ask why the value is missing, then whether this absence should be preserved, filtered, or replaced. Then choose IS NULL, IS NOT NULL or COALESCE depending on the answer.

Finally, check the number of records returned and recalculate a small sample by hand. The exercise dedicated to COALESCE allows you to apply this method on a simple customer table.