IS NULL / IS NOT NULL

-- Retrieves rows where the value is NULL.
SELECT column_name FROM table_name WHERE column_name IS NULL;

-- Retrieves rows where the value is not NULL.
SELECT column_name FROM table_name WHERE column_name IS NOT NULL;

-- Retrieves employees with no retirement date set (NULL) — i.e., current employees.
SELECT name, hire_date FROM employees WHERE retire_date IS NULL;

-- Retrieves employees who have a retirement date set — i.e., former employees.
SELECT name, hire_date, retire_date FROM employees WHERE retire_date IS NOT NULL;

-- Finds employees with no department assigned.
SELECT name FROM employees WHERE department IS NULL;

-- Note the behavior difference when aggregating columns that contain NULL.
-- COUNT(*) counts all rows including NULLs, while COUNT(column) excludes NULLs.
SELECT COUNT(*) AS total_count, COUNT(bonus) AS bonus_count FROM employees;

-- Example using IFNULL (MySQL) to substitute a default value when the column is NULL.
SELECT name, IFNULL(bonus, 0) AS bonus FROM employees;

-- Result of: SELECT name, hire_date FROM employees WHERE retire_date IS NULL;
-- +----------+------------+
-- | name     | hire_date  |
-- +----------+------------+
-- | Tanaka   | 2018-04-01 |
-- | Suzuki   | 2020-07-15 |
-- | Yamada   | 2022-01-10 |
-- +----------+------------+

The IS NULL and IS NOT NULL syntax works consistently across all major databases.

SELECT name, hire_date FROM employees WHERE retire_date IS NULL;

The function used to replace NULL with a fallback value differs by database. The standard SQL COALESCE function works across all databases.

-- Standard SQL (works in all databases)
SELECT name, COALESCE(bonus, 0) AS bonus FROM employees;

MySQL provides the proprietary IFNULL function, and Oracle provides NVL.

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

-- Oracle only
SELECT name, NVL(bonus, 0) AS bonus FROM employees;

In SQL, NULL represents a special state meaning "no value exists" or "unknown." NULL is neither 0 nor an empty string, so evaluating it with standard comparison operators (=, <>, etc.) always returns FALSE.

Using WHERE column = NULL will not retrieve NULL rows. Always use IS NULL instead. Similarly, WHERE column != NULL does not work as expected — use IS NOT NULL.

Arithmetic operations involving NULL (for example, salary + bonus when bonus is NULL) also return NULL. To substitute NULL with a value before calculating, use IFNULL(column, fallback) in MySQL or COALESCE(column, fallback) in standard SQL.