👻 Introduction: The Blank Sticky Note
Imagine you are gathering sign-up forms for a club. The form has a box labeled "Middle Name (Optional)":
- Alice writes: "Jane".
- Bob leaves it completely empty.
When you look at Bob's form, the middle name isn't the word "None" or "Blank". It is an empty slot of missing information.
In databases, this empty state is called NULL.
NULL is a special value that represents unknown, missing, or inapplicable data. Because it is a "ghost" value, it behaves very strangely: if you add 5 to NULL, the result is still NULL (because 5 plus an unknown number is still unknown!).
🛠️ Handling NULLs and Three-Valued Logic
In normal programming languages, logic has two values: True and False. SQL uses Three-Valued Logic: True, False, and Unknown (NULL).
THREE-VALUED LOGIC (AND truth table with NULL)
+---------+---------+---------+---------+
| AND | TRUE | FALSE | NULL |
+---------+---------+---------+---------+
| TRUE | TRUE | FALSE | NULL |
| FALSE | FALSE | FALSE | FALSE |
| NULL | NULL | FALSE | NULL |
+---------+---------+---------+---------+
1. Checking for NULLs: IS NULL and IS NOT NULL
- Bad:
WHERE middle_name = NULL(This will return 0 rows because nothing can equal an unknown!). - Good:
WHERE middle_name IS NULL(Checks if the box is empty).
2. The Fallback Savior: COALESCE()
The COALESCE() function takes a list of columns and returns the first value that is not NULL. It's the ultimate fallback tool!
- Example:
COALESCE(middle_name, 'No Middle Name')returns the student's middle name, but if it is empty, it outputs "No Middle Name" instead.
3. The Equality Eraser: NULLIF()
NULLIF(val1, val2) returns NULL if val1 = val2. Otherwise, it returns val1. (Great for preventing division-by-zero errors: amount / NULLIF(items, 0)).
💻 Code Examples
Let's query users and replace missing email addresses with a default placeholder.
SQL NULL Queries
-- Find users who haven't entered an email
SELECT username FROM users WHERE email IS NULL;
-- Retrieve names, substituting missing nicknames with the username
SELECT username, COALESCE(nickname, username, 'Anonymous') AS display_name
FROM users;Multi-Language Execution
⚠️ Common Mistakes
1. Comparing NULL with standard Operators
Writing WHERE status = NULL or WHERE status <> NULL. In SQL, evaluating NULL = NULL results in NULL (Unknown), not True! Always use IS NULL instead.
- Bad:
SELECT * FROM users WHERE email = NULL; - Good:
SELECT * FROM users WHERE email IS NULL;
2. Math Operations with NULLs
Forgetting that operations like price + tax will result in NULL if either column is NULL. Wrap nullable columns inside COALESCE(column, 0) before doing arithmetic.
🔍 Interview Corner
Q1: What is the result of NULL = NULL in SQL, and why?
The result of NULL = NULL is NULL (Unknown). This is because NULL represents an unknown or missing value, and two unknown values cannot be asserted as equal.
Q2: What is the difference between COALESCE and ISNULL/IFNULL?
COALESCE()is standard ANSI SQL and accepts any number of parameters, returning the first non-null value in the list.IFNULL()(MySQL/SQLite) orISNULL()(SQL Server) are vendor-specific functions that accept exactly two parameters only.
📝 Practice Links
Explore related coding challenges on the platform:
📝 Summary
NULLrepresents missing, empty, or unknown values.- Never use
=to check forNULL. UseIS NULLorIS NOT NULL. - Use
COALESCE(val1, val2, ...)to set a fallback default value for empty fields.