SQL NULL Values

NULL means no value: unknown, missing or not applicable. It is not zero and not an empty string, and it does not behave like a normal value. Comparisons with NULL are never true, arithmetic with NULL gives NULL, and aggregates skip it. Most “my query lost some rows” bugs come from NULLs.

What NULL means

In the sample data, NULL appears where a value does not exist:

• the CEO has no department and no manager;
• only sales staff have a commission;
• David Fernandes has no email.

IS NULL and IS NOT NULL

Employees without a commission:

SELECT first_name, commissionFROM employeesWHERE commission IS NULL;

Employees with a commission:

SELECT first_name, commissionFROM employeesWHERE commission IS NOT NULL;

Why = NULL never works

SELECT first_nameFROM employeesWHERE commission = NULL;

You can see the three-valued logic directly:

SELECT NULL = NULL   AS equals,       NULL <> 1     AS not_equal,       NULL IS NULL  AS is_null,       NULL <=> NULL AS null_safe_equals;

NULL in arithmetic

Any arithmetic with NULL gives NULL:

SELECT first_name, salary, commission,       salary + commission AS total_payFROM employeesWHERE dept_id IN (1, 2)ORDER BY emp_id;

SELECT first_name, salary + COALESCE(commission, 0) AS total_payFROM employeesWHERE dept_id IN (1, 2)ORDER BY emp_id;

NULL in aggregates

Aggregate functions skip NULLs, except COUNT(*), which counts rows:

SELECT COUNT(*)          AS all_rows,       COUNT(commission) AS with_commission,       SUM(commission)   AS total_commission,       AVG(commission)   AS avg_commissionFROM employees;

NULL in sorting and DISTINCT

• In MySQL, NULLs sort first in ascending order and last in descending order (ORDER BY).
• DISTINCT and GROUP BY treat all NULLs as one group (DISTINCT).

SELECT dept_id, COUNT(*) AS staffFROM employeesGROUP BY dept_idORDER BY dept_id;

NULL vs empty string vs zero

CREATE TABLE contacts (name VARCHAR(20), phone VARCHAR(15));INSERT INTO contacts VALUES ('Asha', '98450 12345'), ('Ravi', ''), ('Mona', NULL);SELECT name,       phone IS NULL  AS is_null,       phone = ''     AS is_empty,       LENGTH(phone)  AS lenFROM contacts;

NOT NULL columns

If a column must always have a value, declare it NOT NULL when you create the table, and the database will refuse missing values (NOT NULL constraint).

SELECT customer_id, customer_nameFROM customersWHERE email IS NULL;

SELECT first_name, salary + COALESCE(commission, 0) AS total_payFROM employeesORDER BY total_pay DESC;

NULL values FAQ