SQL — Null Values

What is NULL?

NULL represents missing or unknown data. It's not zero, not an empty string, and not false — it's the absence of a value.

NULL vs Zero vs Empty String

CREATE TABLE demo (
  val_int INT,
  val_str VARCHAR(10)
);

INSERT INTO demo VALUES (0, ''), (NULL, NULL);

SELECT * FROM demo;
-- | val_int | val_str |
-- | 0       |         |
-- | NULL    | NULL    |

Checking for NULL

You cannot use = or != with NULL. Use IS NULL and IS NOT NULL:

-- Wrong: returns nothing
SELECT * FROM employees WHERE phone = NULL;

-- Right
SELECT * FROM employees WHERE phone IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

NULL in Comparisons

Any comparison with NULL returns UNKNOWN (not TRUE or FALSE):

SELECT NULL = NULL;    -- UNKNOWN (not TRUE!)
SELECT NULL != NULL;   -- UNKNOWN
SELECT NULL > 5;       -- UNKNOWN
SELECT NULL + 10;      -- NULL

NULL in WHERE

-- Find employees without a phone number
SELECT first_name, last_name
FROM employees
WHERE phone IS NULL;

-- Find employees who have an email
SELECT first_name
FROM employees
WHERE email IS NOT NULL;

NULL in ORDER BY

NULL values sort differently by database:

-- MySQL: NULLs sort first (ascending)
-- PostgreSQL: NULLs sort last (ascending)
SELECT * FROM employees ORDER BY phone ASC;

-- Force consistent ordering
SELECT * FROM employees ORDER BY phone IS NULL, phone ASC;

NULL in Aggregate Functions

Most aggregate functions ignore NULL:

CREATE TABLE scores (student_id INT, score INT);
INSERT INTO scores VALUES (1, 90), (2, NULL), (3, 85);

SELECT
  COUNT(*) AS total_rows,     -- 3 (counts all)
  COUNT(score) AS non_null,   -- 2 (ignores NULL)
  AVG(score) AS average       -- 87.5 (ignores NULL)
FROM scores;

COALESCE: Replace NULL

SELECT
  first_name,
  COALESCE(phone, 'No phone') AS phone,
  COALESCE(email, 'No email') AS email
FROM employees;

COALESCE returns the first non-NULL value:

COALESCE(a, b, c) -- returns a if not NULL, else b if not NULL, else c

NULLIF: Create NULL

-- Returns NULL if values are equal
SELECT NULLIF(10, 10);  -- NULL
SELECT NULLIF(10, 20);  -- 10

-- Useful to avoid division by zero
SELECT revenue / NULLIF(quantity, 0) AS unit_price
FROM sales;

IFNULL (MySQL) / NVL (Oracle)

-- MySQL
SELECT IFNULL(phone, 'N/A') FROM employees;

-- Oracle
SELECT NVL(phone, 'N/A') FROM employees;

NULL with DISTINCT

SELECT DISTINCT department FROM employees;
-- Includes NULL as one distinct value

NULL in IN / NOT IN

-- NULL is NOT in a list (it's never equal to anything)
WHERE department IN (1, 2, 3)  -- NULL department is excluded
WHERE department NOT IN (1, 2) -- NULL department is excluded too!

Best Practices

• Always use IS NULL / IS NOT NULL — never = NULL
• Use COALESCE to provide sensible defaults for display
• Set NOT NULL constraints on columns that must have values
• Use NULLIF(value, 0) to prevent division by zero
• Be careful with NOT IN when the subquery may return NULLs

Practice

1. Find all products without a description
2. Replace NULL values in a query with "N/A"
3. Calculate average salary excluding NULLs
4. Use NULLIF to safely divide by a potentially zero value