SQL COALESCE and NULLIF: Practical NULL Handling

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

COALESCE(city,'Unknown') returns the saved city when present and the chosen label when absent. Several arguments are allowed, so COALESCE(primary_phone,backup_phone,'No phone') expresses a fallback chain. It does not change the stored columns. A known empty string remains the selected value because empty text is not NULL in these engines. If empty text should count as missing, convert it deliberately with NULLIF before applying the fallback.

NULLIF(x,y) acts like a comparison-based missing-value expression. If x equals y, the result is NULL; otherwise it returns x. NULLIF(denominator,0) is a common way to avoid a zero denominator. NULLIF(TRIM(label),'') turns a whitespace-trimmed empty label into missing text. That is a data policy, so ensure a genuine empty label is actually supposed to be treated as absent. The two arguments must have types the database can compare sensibly.

Placement around an aggregate matters. COALESCE(SUM(amount),0) converts an unavailable sum to zero after normal aggregate processing. SUM(COALESCE(amount,0)) changes each missing input amount to zero before addition. Their outputs can coincide in a simple total while expressing different assumptions and behaving differently over empty input. For AVG, the distinction is directly visible: replacing missing input with zero increases the denominator, while replacing only a missing final average does not.

Type resolution considers the expressions in the function, not just the value chosen on one sample row. Mixing a numeric amount with a text fallback such as 'Unknown' can fail in strongly typed engines or trigger an unwanted conversion. Keep numeric computations numeric, and create display text separately. If a ratio becomes NULL because its denominator is zero, immediately converting that to zero can hide the reason it is undefined. A clear unavailable label may be more truthful than a numeric default.

COALESCE and NULLIF are supported by all four systems. SQL Server ISNULL, MySQL IFNULL and SQLite ifnull are related dialect functions, but their type rules or argument limits differ. Prefer the standard-style functions when sharing queries, and check return-type behaviour when arguments have different numeric or text types.

Check the engine-specific syntax and version requirements in PostgreSQL conditional expressions.

Show the known amount per sale and a safe ratio amount divided by amount. Exclude missing amounts for this exercise and use NULLIF to handle any future zero amount.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT id,amount/NULLIF(amount,0) AS self_ratio
FROM sales WHERE amount IS NOT NULL ORDER BY id;

id | self_ratio
101 | 1
102 | 1
103 | 1
105 | 1
106 | 1

Every supplied known amount is nonzero, so division by itself yields one. If a zero were added, NULLIF would convert its denominator to NULL and the ratio would be NULL. It would not label the undefined zero-divided-by-zero case as a known one.

Evaluate COALESCE('','Unknown') and COALESCE(NULL,'Unknown') to see that empty text remains present. Then test COALESCE(NULLIF(TRIM(' '),''),'Unknown') to observe an explicit whitespace policy. Compare AVG(COALESCE(amount,0)) with COALESCE(AVG(amount),0) on the sample sales: their different denominators make the placement of the fallback visible.