How do you compare NULLs in SQL?

You must use IS NULL or IS NOT NULL. Using = NULL or <> NULL does not return expected results because NULL is not equal to anything.

Handling NULL Values in SQL Comparisons | FullStackPrep.Dev

Q: What does COALESCE do?

COALESCE returns the first non-null value from a list of expressions, and is a standard way to handle NULLs in SQL.

💡 Concept Name

NULL Handling in SQL – Using correct predicates and functions like IS NULL, IS NOT NULL, COALESCE(), and ISNULL() to handle unknown values.

📘 Quick Intro

In SQL, NULL represents unknown or missing data. Standard comparison operators like = and <> don’t work as expected with NULL. Instead, use IS NULL or IS NOT NULL for reliable filtering.

🧠 Analogy / Short Story

Imagine someone asks for your favorite movie, but you haven't watched any. Saying "My favorite movie is NULL" doesn’t mean "no movie" — it means "I don't know." Comparing unknowns is like comparing two blank pieces of paper — you can't tell if they're the same without content. That’s why SQL treats NULLs differently.

🔧 Technical Explanation

NULL indicates an unknown value in SQL.
Comparisons like column = NULL or column <> NULL always result in UNKNOWN.
Use IS NULL or IS NOT NULL to filter NULLs.
COALESCE(expr1, expr2, ...) returns the first non-NULL expression.
ISNULL(expr, replacement) replaces NULL with a specified default value (SQL Server specific).

💻 Code Examples

-- Find rows where ManagerID is NULL
SELECT * FROM Employees
WHERE ManagerID IS NULL;

-- Replace NULL with default
SELECT ISNULL(PhoneNumber, 'No Phone') AS ContactPhone
FROM Customers;

-- Use COALESCE to choose first non-null
SELECT COALESCE(MiddleName, FirstName, 'Unknown') AS DisplayName
FROM Employees;

❓ Interview Q&A

Q1: What does NULL mean in SQL?
A: NULL represents an unknown or missing value. It is not equivalent to an empty string or zero.

Q2: Can we compare NULL using = or <>?
A: No. Using = NULL or <> NULL results in UNKNOWN.

Q3: How do you properly check for NULL in SQL?
A: Use IS NULL or IS NOT NULL predicates.

Q4: What is the use of COALESCE()?
A: It returns the first non-null value from the list of expressions.

Q5: What is ISNULL() used for?
A: In SQL Server, it replaces NULL with a specified value.

Q6: Is NULL equal to NULL?
A: No. NULL is not equal to anything, not even another NULL.

Q7: How does GROUP BY treat NULLs?
A: It groups all NULLs into a single group.

Q8: Can we sort NULLs in SQL?
A: Yes. Most databases place NULLs either first or last when sorting.

Q9: How can we avoid NULL-related bugs?
A: Always check for NULL explicitly using proper predicates or defaulting functions.

Q10: Does COALESCE work in all databases?
A: Yes. It's part of the ANSI SQL standard.

📝 MCQs

Q1. What does NULL represent in SQL?

Zero
Empty string
Unknown or missing value
Space

Q2. How do you check if a column is NULL?

Using = NULL
Using IS NULL
Using == NULL
Using != NULL

Q3. What does COALESCE do?

Returns last NULL
Returns sum
Returns first non-null value
Skips NULLs

Q4. What does ISNULL(Phone, 'N/A') return if Phone is NULL?

NULL
'N/A'
0
False

Q5. Is NULL equal to NULL in SQL?

Yes
Sometimes
No
Depends on DB

Q6. What is the result of NULL = NULL?

TRUE
FALSE
UNKNOWN
NULL

Q7. How does GROUP BY treat NULL values?

Ignores NULLs
Skips NULLs
Groups all NULLs together
Throws error

Q8. Can NULLs be sorted?

No
Yes
Sometimes
Only in MySQL

Q9. What is the default value used by ISNULL(column, 'Default') if column is NULL?

NULL
'Default'
0
Empty

Q10. Which function is ANSI standard for handling NULLs?

ISNULL
NVL
COALESCE
DECODE

💡 Bonus Insight

Always handle NULLs explicitly in SQL queries to avoid incorrect filters or logic bugs. Use IS NULL, COALESCE(), and ISNULL() where appropriate to maintain data integrity and readability.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

📄 PDF Download Coming Soon

Handling NULL Values in SQL Comparisons