SQL NULL Handling - 340 Employees Missing Bonuses

Every real-world database has missing data. A customer who didn't provide a phone number. An order with no shipping date yet because it hasn't shipped. An employee whose bonus hasn't been decided. These gaps are everywhere, and SQL has a special way of representing them — NULL. If you don't understand NULL, your queries will silently return wrong answers without throwing a single error, which is one of the most dangerous bugs a database can have.

The tricky part is that NULL breaks the rules you're used to. In normal life, if you ask 'is blank equal to blank?' you'd say yes. SQL says 'I don't know' — and that 'I don't know' answer is what causes queries to behave unexpectedly. Filters exclude rows you expected to include, calculations return NULL when you expected a number, and JOINs quietly drop data. SQL gives you specific tools — IS NULL, IS NOT NULL, COALESCE, and NULLIF — to handle these gaps safely and deliberately.

By the end of this article you'll understand what NULL actually means (and what it doesn't), why you can't use = NULL in a WHERE clause, how to filter and replace NULLs with confidence, and how to avoid the silent bugs that trip up even experienced developers. You'll be writing NULL-safe SQL from scratch.

What NULL Actually Is — and Why = NULL Never Works

NULL isn't a value — it's the absence of a value. Think of it like a sealed envelope. You don't know what's inside. If someone hands you two sealed envelopes and asks 'are these the same?', the honest answer is 'I have no idea' — not yes, not no. SQL agrees. Comparing anything to NULL using = produces a third logical state called UNKNOWN, which is neither TRUE nor FALSE.

This is why WHERE salary = NULL will never return any rows, even if the salary column genuinely contains NULLs. SQL evaluates the condition, gets UNKNOWN for every row, and excludes them all. WHERE only keeps rows where the condition is TRUE.

SQL gives you a dedicated operator for NULL checks: IS NULL and IS NOT NULL. These are the only correct ways to test whether a value is absent. They return TRUE or FALSE — no UNKNOWN — which is exactly what WHERE needs to filter rows correctly.

This also means NULL = NULL is NOT true in SQL. Two unknown things are not automatically equal. This catches beginners off guard constantly, especially when writing JOIN conditions.

-- Create a simple employees table to demonstrate NULL behavior
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    department    VARCHAR(50),
    phone_number  VARCHAR(20),   -- nullable: not every employee has provided one
    annual_bonus  DECIMAL(10,2)  -- nullable: bonus not yet decided for some staff
);

-- Insert sample rows — some have NULLs, some don't
INSERT INTO employees VALUES (1, 'Alice Mercer',   'Engineering', '555-0101', 4500.00);
INSERT INTO employees VALUES (2, 'Ben Okafor',     'Marketing',   NULL,       3200.00);
INSERT INTO employees VALUES (3, 'Clara Huang',    'Engineering', '555-0303', NULL);
INSERT INTO employees VALUES (4, 'David Nkosi',    'HR',          NULL,       NULL);
INSERT INTO employees VALUES (5, 'Eva Johansson',  'Marketing',   '555-0505', 2800.00);

-- ❌ WRONG: This returns 0 rows even though NULLs exist in phone_number
-- WHERE evaluates phone_number = NULL as UNKNOWN for every row
SELECT full_name
FROM   employees
WHERE  phone_number = NULL;
-- Output: (0 rows) — silent bug!

-- ✅ CORRECT: Use IS NULL to find employees without a phone number
SELECT full_name
FROM   employees
WHERE  phone_number IS NULL;
-- Output: Ben Okafor, David Nkosi

-- ✅ CORRECT: Use IS NOT NULL to find employees who DO have a phone number
SELECT full_name, phone_number
FROM   employees
WHERE  phone_number IS NOT NULL;
-- Output: Alice Mercer | 555-0101
--         Clara Huang  | 555-0303
--         Eva Johansson| 555-0505

COALESCE — Replacing NULL With a Sensible Default

Now that you can detect NULLs, the next question is: what do you do with them? Often you want to replace a NULL with a default value so your reports and application logic get something useful instead of a blank.

That's exactly what COALESCE does. You give it a list of values and it hands back the first one that isn't NULL. Think of it like a backup plan — 'give me the first option, but if that's blank, try the second, and if that's blank too, try the third.'

Coalesce is standard SQL and works in MySQL, PostgreSQL, SQL Server, SQLite, and Oracle. It's one of the most-used NULL-handling functions in production databases.

A key use case is calculated columns. If annual_bonus is NULL and you add it to a base salary, the entire result becomes NULL — SQL says 'I can't add a known number to an unknown number and give you a real answer.' COALESCE lets you substitute zero (or any default) so your arithmetic stays intact.

COALESCE can take more than two arguments. COALESCE(a, b, c, d) returns the first non-NULL across all four — really useful when you have primary, secondary, and fallback data sources.

-- COALESCE(value_to_check, fallback_if_null)
-- Returns the first argument that is NOT NULL

-- Use case 1: Display a friendly label instead of NULL in a report
SELECT
    full_name,
    COALESCE(phone_number, 'No phone on file') AS contact_number
FROM employees;

-- Use case 2: Safe arithmetic — treat a missing bonus as 0
-- Without COALESCE, NULL bonus would make the entire total NULL
SELECT
    full_name,
    80000                             AS base_salary,
    COALESCE(annual_bonus, 0)         AS bonus,           -- swap NULL for 0
    80000 + COALESCE(annual_bonus, 0) AS total_compensation
FROM employees;

-- Use case 3: Multiple fallbacks — try preferred_email, then work_email, then a default
-- (Imaginary columns to show the pattern)
-- COALESCE(preferred_email, work_email, 'no-email@company.com')

-- Use case 4: COALESCE in a WHERE clause to filter on computed values
SELECT full_name
FROM   employees
WHERE  COALESCE(annual_bonus, 0) < 3000;  -- treats NULL bonus as 0 for comparison

NULLIF and Aggregate Functions — NULLs You Create on Purpose

COALESCE turns NULLs into real values. NULLIF does the opposite — it turns a specific real value into NULL. Why on earth would you want that?

The classic example is preventing division by zero. If you divide by zero in SQL, you get an error. But if you use NULLIF(denominator, 0), SQL turns any zero denominator into NULL — and dividing by NULL returns NULL instead of crashing. You can then wrap that in COALESCE to display something readable.

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX have a specific and important relationship with NULL: they all silently skip NULL values. This is usually what you want — averaging a salary column where some salaries aren't set yet should average the known salaries, not treat unknowns as zero. But it means COUNT(*) and COUNT(column_name) give different answers, which surprises many beginners.

COUNT(*) counts every row. COUNT(phone_number) counts only rows where phone_number is not NULL. This distinction matters enormously in reports.

-- NULLIF(expression, unwanted_value)
-- Returns NULL if expression equals unwanted_value, otherwise returns expression

-- Use case: safe division — avoid divide-by-zero error
-- Imagine a sales table with targets and actual sales
CREATE TABLE sales_performance (
    rep_name    VARCHAR(100),
    target      INT,
    actual_sold INT
);

INSERT INTO sales_performance VALUES ('Nia Patel',    100, 85);
INSERT INTO sales_performance VALUES ('Omar Silva',   0,   0);  -- new rep, no target set
INSERT INTO sales_performance VALUES ('Priya Chen',   200, 210);

-- ❌ WRONG: This crashes when target = 0
-- SELECT actual_sold / target FROM sales_performance;

-- ✅ CORRECT: NULLIF converts 0 target to NULL, avoiding the crash
-- COALESCE then turns the NULL result into a readable message
SELECT
    rep_name,
    target,
    actual_sold,
    COALESCE(
        CAST(actual_sold AS DECIMAL) / NULLIF(target, 0) * 100,
        0
    ) AS achievement_pct   -- 0 when target was NULL/zero
FROM sales_performance;

-- ─────────────────────────────────────────
-- Aggregate functions and NULL — key demo
-- ─────────────────────────────────────────

-- COUNT(*) counts ALL rows including those with NULLs
SELECT COUNT(*) AS total_employees
FROM   employees;
-- Returns: 5

-- COUNT(column) counts only NON-NULL values in that column
SELECT COUNT(annual_bonus) AS employees_with_bonus
FROM   employees;
-- Returns: 3  (Clara and David have NULL bonus — they are skipped)

-- AVG also skips NULLs — it averages only the 3 known bonuses
SELECT AVG(annual_bonus) AS average_known_bonus
FROM   employees;
-- Returns: 3500.00  (4500 + 3200 + 2800) / 3

-- If you want NULL bonuses treated as 0 in the average:
SELECT AVG(COALESCE(annual_bonus, 0)) AS average_bonus_including_none
FROM   employees;
-- Returns: 2100.00  (4500 + 3200 + 0 + 0 + 2800) / 5

The Nullability Tax — Why Your Indexes Lie to You

You've been burned by this before: a query that should fly runs a full table scan. You check the execution plan, and there it is — a NULL-friendly column wrecking your index selectivity. NULLs are not values, but SQL Server still stores them. In a B-tree index, every NULL goes into the same linked list. That means a seek becomes a scan of all NULL rows before you find anything useful.

This isn't a bug. It's a design decision that punishes lazy schema design. If you filter WHERE column IS NULL, the engine can't do a simple equality check. It has to chase pointers. The fix? Filtered indexes. CREATE INDEX idx_active_orders ON orders (status) WHERE status IS NOT NULL. That index only lives where the data exists. Your query suddenly becomes logarithmic instead of linear.

The WHY is simple: NULL breaks the binary assumption of value comparison. Indexes rely on ordered value sets. NULL has no order. Don't let your database pretend otherwise.

// io.thecodeforge — database tutorial

CREATE TABLE orders (
    order_id INT IDENTITY PRIMARY KEY,
    status VARCHAR(20) NULL,
    total DECIMAL(10,2)
);

// Without filtered index — full scan on status
SELECT order_id FROM orders WHERE status = 'SHIPPED';

// Create filtered index for non-null statuses only
CREATE INDEX idx_orders_shipped
    ON orders (status)
    WHERE status IS NOT NULL;

// Now the seek works 
SELECT order_id FROM orders WHERE status = 'SHIPPED';

Three-Valued Logic — The Boolean Trap That Eats Joins

Here's where juniors burn hours debugging: you write a LEFT JOIN with a WHERE clause, and rows vanish. The culprit is three-valued logic. SQL doesn't do true/false like your programming language. It has TRUE, FALSE, and UNKNOWN. Any comparison with NULL produces UNKNOWN. And WHERE clauses only return rows where the condition is TRUE. UNKNOWN is treated as false.

Example: SELECT * FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.total > 100. This kills all customers with no orders — because NULL > 100 is UNKNOWN, not FALSE. The join returns them, but the WHERE filter discards them. You just turned a LEFT JOIN into an INNER JOIN without knowing it.

The fix is explicit: move the condition into the JOIN clause itself. LEFT JOIN orders o ON c.id = o.customer_id AND o.total > 100. Or use COALESCE to provide a default. But understand the logic first. Every time you write a WHERE clause on a nullable column from an outer join, you're playing Russian roulette with your result set.

// io.thecodeforge — database tutorial

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NULL,
    total DECIMAL(10,2)
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO orders VALUES (100, 1, 250.00), (101, NULL, NULL);

// The broken query — Alice vanishes
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100;

-- Output: (empty set if no orders match)

// The correct query
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.total > 100;

A Real-World Scenario

Why bother with NULL handling? Because NULLs silently break business logic in production. Consider an employee bonus system: your query calculates annual bonus as base_salary * performance_multiplier. If performance_multiplier is NULL (manager didn't submit review), the result is NULL, not zero. Payroll runs, employees with missing reviews get zero bonus — without any error or warning. The real cost hits when accounting runs reconciliation: the total bonus payout doesn't match the query output by thousands of dollars. You need to catch these NULL-drift paths before they corrupt downstream reports.

// io.thecodeforge — database tutorial

-- NULL multiplies through: NULL * 50000 = NULL, not 0
SELECT 
  employee_id,
  base_salary * performance_multiplier AS bonus_nulls,
  COALESCE(base_salary * performance_multiplier, 0) AS bonus_safe
FROM employee_performance
WHERE performance_multiplier IS NULL;

Wrapping Up

Three lessons to carry forward. First, NULL is not a value — it's a marker for the absence of a value, and SQL's three-valued logic (TRUE, FALSE, UNKNOWN) means your WHERE clauses can silently exclude rows you expect to include. Second, use COALESCE to replace NULLs with defaults at query output, but never inside JOIN conditions — that masks missing relationships and corrupts referential integrity. Third, NULLIF and NULL-handling in aggregates let you intentionally exclude data (e.g., divide-by-zero prevention). Every NULL in your schema imposes a cognitive tax: each query writer must remember where NULLs live and how they propagate. Normalize your data to avoid optional columns where possible, and document every nullable column's business meaning in your schema comments.

// io.thecodeforge — database tutorial

-- Final pattern: safe aggregation with NULL handling
SELECT 
  department_id,
  COUNT(*) AS total_employees,
  COUNT(salary) AS employees_with_salary,
  AVG(COALESCE(bonus, 0)) AS avg_bonus_with_default
FROM employees
GROUP BY department_id;

NULL vs DBNULL vs DEFAULT: Database-Specific Behavior

Different databases treat NULL, DBNULL, and DEFAULT values in distinct ways, which can lead to subtle bugs when porting SQL code. In SQL Server, DBNull is a .NET concept representing a database null, but in T-SQL, NULL is the only representation. MySQL treats NULL and DEFAULT differently: a column defined with a DEFAULT value will use that value when no explicit value is provided, but inserting NULL explicitly stores NULL, not the default. PostgreSQL follows the SQL standard strictly: NULL means unknown, and DEFAULT is used only when the column is omitted in an INSERT. For example, in PostgreSQL:

```sql CREATE TABLE employees ( id SERIAL PRIMARY KEY, name TEXT, bonus NUMERIC DEFAULT 0 );

INSERT INTO employees (name) VALUES ('Alice'); -- bonus = 0 (DEFAULT) INSERT INTO employees (name, bonus) VALUES ('Bob', NULL); -- bonus = NULL ```

In MySQL, the same behavior applies, but with a nuance: if the column is defined as NOT NULL DEFAULT 0, inserting NULL will cause an error or be converted to the default depending on the SQL mode. SQL Server behaves similarly to PostgreSQL but with ISNULL() as a T-SQL extension. Understanding these differences is crucial when writing cross-database applications or migrating data.

-- PostgreSQL: NULL vs DEFAULT
CREATE TABLE test (
    id INT,
    val INT DEFAULT 0
);

INSERT INTO test (id) VALUES (1);      -- val = 0 (DEFAULT)
INSERT INTO test (id, val) VALUES (2, NULL); -- val = NULL
INSERT INTO test (id, val) VALUES (3, DEFAULT); -- val = 0 (explicit DEFAULT)

SELECT * FROM test;

-- MySQL: NULL vs DEFAULT (strict mode off)
INSERT INTO test (id, val) VALUES (4, NULL); -- val = NULL
INSERT INTO test (id) VALUES (5);            -- val = 0 (DEFAULT)

-- SQL Server: similar to PostgreSQL
INSERT INTO test (id, val) VALUES (6, NULL); -- val = NULL
INSERT INTO test (id) VALUES (7);            -- val = 0 (DEFAULT)

NULL in Indexes: PostgreSQL vs MySQL vs SQL Server

How databases index NULL values varies significantly and affects query performance. PostgreSQL treats NULLs as distinct values and includes them in B-tree indexes by default. In PostgreSQL, NULLs are considered larger than all non-null values (unless using NULLS FIRST/LAST). MySQL's InnoDB also indexes NULLs, but they are stored at the beginning of the index (like NULLS FIRST). SQL Server indexes NULLs as well, but they are treated as equal to each other and are stored at the end of the index (like NULLS LAST). This difference impacts queries with IS NULL or IS NOT NULL conditions. For example, in PostgreSQL, an index on a nullable column can efficiently support WHERE col IS NULL because NULLs are indexed. In SQL Server, the same index also supports IS NULL efficiently. However, in MySQL, IS NULL can use the index but may require scanning a range of NULL entries. A critical nuance: in PostgreSQL, a unique index allows multiple NULL values (since NULL != NULL), while in SQL Server and MySQL, a unique constraint also allows multiple NULLs. But in older MySQL versions (before 5.7), a unique index allowed only one NULL. Always check your database version. For partial indexes (PostgreSQL), you can create an index that only includes rows where the column is NULL, improving performance for sparse null checks.

-- PostgreSQL: index on nullable column
CREATE INDEX idx_bonus ON employees(bonus);
EXPLAIN ANALYZE SELECT * FROM employees WHERE bonus IS NULL;
-- Uses index scan

-- MySQL: similar index
CREATE INDEX idx_bonus ON employees(bonus);
EXPLAIN SELECT * FROM employees WHERE bonus IS NULL;
-- May use index, but NULLs are stored first

-- SQL Server: index on nullable column
CREATE INDEX idx_bonus ON employees(bonus);
SET STATISTICS IO ON;
SELECT * FROM employees WHERE bonus IS NULL;
-- Uses index seek

-- PostgreSQL partial index for NULLs only
CREATE INDEX idx_bonus_null ON employees(bonus) WHERE bonus IS NULL;
EXPLAIN ANALYZE SELECT * FROM employees WHERE bonus IS NULL;
-- Very efficient for sparse NULLs

COALESCE, NULLIF, ISNULL: Function Comparison Across Dialects

SQL provides several functions to handle NULLs, but their syntax and behavior vary across databases. COALESCE is standard SQL and returns the first non-null argument. It is supported in PostgreSQL, MySQL, SQL Server, and others. NULLIF returns NULL if two expressions are equal, otherwise returns the first expression. ISNULL is a T-SQL function in SQL Server that replaces NULL with a specified value, but it is not standard. MySQL has IFNULL which does the same. PostgreSQL does not have ISNULL; use COALESCE instead. For example:

```sql -- COALESCE: standard, works everywhere SELECT COALESCE(bonus, 0) AS effective_bonus FROM employees;

-- NULLIF: returns NULL if equal SELECT NULLIF(commission, 0) AS non_zero_commission FROM sales;

-- ISNULL (SQL Server only) SELECT ISNULL(bonus, 0) FROM employees;

-- IFNULL (MySQL only) SELECT IFNULL(bonus, 0) FROM employees; ```

A common pitfall: ISNULL in SQL Server takes only two arguments, while COALESCE can take multiple. Also, COALESCE evaluates arguments lazily in some databases, but not all. In PostgreSQL, COALESCE evaluates all arguments before checking for NULL, which can cause side effects if using functions. NULLIF is often used to avoid division by zero: NULLIF(denominator, 0). In MySQL, IFNULL is limited to two arguments, so use COALESCE for more. For cross-database compatibility, prefer COALESCE and NULLIF over vendor-specific functions.

-- COALESCE: standard, multi-argument
SELECT COALESCE(bonus, commission, 0) AS payment FROM employees;

-- NULLIF: prevent division by zero
SELECT amount / NULLIF(quantity, 0) AS unit_price FROM sales;

-- SQL Server ISNULL (two args only)
SELECT ISNULL(bonus, 0) FROM employees;

-- MySQL IFNULL (two args only)
SELECT IFNULL(bonus, 0) FROM employees;

-- PostgreSQL: no ISNULL, use COALESCE
SELECT COALESCE(bonus, 0) FROM employees;

-- Oracle: NVL (two args) or NVL2 (three args)
SELECT NVL(bonus, 0) FROM employees;
SELECT NVL2(bonus, 'has bonus', 'no bonus') FROM employees;