NULL: Missing Values
NULL means no value. It is not zero, and it is not empty text. It means the value is missing or unknown.
In the last lesson, Alex was added without a department or a gender. Let's add him again and look at the result:
INSERT INTO employees (id, name, age)
VALUES (22, 'Alex', 27);
SELECT * FROM employees WHERE id > 19;
Output:
INSERT 0 1
id | name | age | department | gender
----+--------+-----+------------+--------
20 | Thomas | 33 | IT | male
22 | Alex | 27 | |
(2 rows)
Alex's department and gender are NULL. psql shows them as empty spaces.
NULL is not equal to anything
You might try to find the missing departments with = NULL. It does not work:
SELECT name FROM employees
WHERE department = NULL;
Output:
name
------
(0 rows)
No rows came back. NULL means "unknown", and PostgreSQL cannot say that an unknown value is equal to anything, not even to another NULL.
Use IS NULL and IS NOT NULL
To check for NULL, use IS NULL:
SELECT name FROM employees
WHERE department IS NULL;
Output:
name
------
Alex
(1 row)
IS NOT NULL does the opposite. It keeps the rows that have a value.
Replace NULL with COALESCE
COALESCE(value, other) returns the first value that is not NULL. Use it to show a default:
SELECT name, COALESCE(department, 'Unknown') AS department
FROM employees
WHERE id > 19;
Output:
name | department
--------+------------
Thomas | IT
Alex | Unknown
(2 rows)
Tip: NULL and NOT
Be careful with NOT and !=. They also skip NULL rows:
SELECT name FROM employees
WHERE department != 'IT' AND id > 19;
Output:
name
------
(0 rows)
Alex is not in IT, but he is not in the result. His department is unknown, so PostgreSQL cannot say it is different from 'IT'. To include him, add OR department IS NULL.
In the exercise, find the employees whose gender is missing.