SQL - NULL & IS NULL

Overview

NULL represents missing/unknown. Comparisons with NULL result in UNKNOWN; use IS NULL/IS NOT NULL.

SELECT COALESCE(hired_at, NOW()) AS effective_hired
FROM employees
WHERE hired_at IS NULL;

SELECT ISNULL(hired_at, SYSDATETIME()) AS effective_hired
FROM employees
WHERE hired_at IS NULL;

SELECT COALESCE(hired_at, datetime('now')) AS effective_hired
FROM employees
WHERE hired_at IS NULL;

Prerequisite

Load the Standard Test Data.

Expected Output

All employees with NULL hired_at (only Chen in the seed data) are replaced with current time in the result:

effective_hired
---------------------------
2021-01-10 10:00:00  -- Ada (not NULL, unchanged)
2021-05-05 09:30:00  -- Bob (not NULL, unchanged)
⟨current_time⟩      -- Chen (NULL → coalesced)
2022-03-01 12:00:00  -- Dee
2020-11-20 15:15:00  -- Eli