NULL vs Empty String vs Zero

basic

Understanding the difference between NULL, empty string, and zero

-- EMPLOYEES table:

employee_id | name | phone | bonus | manager_id

1 | Alice | 555-1234 | 5000 | NULL

2 | Bob | '' | 0 | 1

3 | Carol | NULL | NULL | 1

-- Find employees without phone:

SELECT * FROM employees WHERE phone IS NULL;

-- Returns: Carol

-- Find employees with no bonus:

SELECT * FROM employees WHERE bonus = 0 OR bonus IS NULL;

-- Returns: Bob, Carol

NULL ≠ empty string ≠ zero

• NULL: Unknown/missing

• Empty string: Known but empty

• Zero: Known numeric value

NULL is absence of value. Empty string is a value. Use IS NULL to check for NULL.

All

Three-Valued Logic

intermediate

Understanding TRUE, FALSE, UNKNOWN in SQL

-- Truth table:

NULL = NULL → UNKNOWN

NULL != 5 → UNKNOWN

NULL > 10 → UNKNOWN

NULL IS NULL → TRUE

NULL IS NOT NULL → FALSE

-- WHERE clause behavior:

WHERE age > 30

• age = 35 → TRUE → row included

• age = 25 → FALSE → row excluded

• age = NULL → UNKNOWN → row excluded

-- Logical operations:

TRUE AND NULL → UNKNOWN

FALSE AND NULL → FALSE

TRUE OR NULL → TRUE

FALSE OR NULL → UNKNOWN

WHERE only includes TRUE results.