Handle NULL Values in SQL

Example query

This example assumes: order_items(id, order_id bigint, discount numeric, quantity numeric, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT COUNT(*) AS rows_total,
       COUNT(discount) AS rows_with_discount,
       AVG(discount) AS avg_ignoring_nulls,
       AVG(COALESCE(discount, 0)) AS avg_nulls_as_zero,
       SUM(total_amount) / NULLIF(SUM(quantity), 0) AS revenue_per_unit
FROM order_items;

How it works

• COUNT(*) counts all rows; COUNT(discount) counts only the rows where discount is not null.
• AVG ignores nulls, so it averages only the rows that have a discount; COALESCE(discount, 0) treats a missing discount as zero and gives a lower average.
• NULLIF(SUM(quantity), 0) returns null instead of zero, so the division returns null rather than an error.

MySQL, BigQuery, SQL Server and SQLite differences

• COALESCE and NULLIF are standard SQL and work everywhere. SQL Server also has ISNULL and MySQL has IFNULL, which take two arguments only.

Common mistakes

• Null is not zero: choosing between ignoring and zero-filling changes averages and should be a deliberate decision.
• WHERE col = NULL never matches; use IS NULL, and IS DISTINCT FROM when comparing nullable columns.
• NOT IN with a subquery that contains a null returns no rows; use NOT EXISTS instead.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• What share of order items have no discount recorded?
• Compare the average discount with and without missing values treated as zero.