Beginner LevelSQL & MySQL
How does SQL handle NULL values using IS NULL and COALESCE()?
❓ Interview Prompt: Explain Three-Valued Logic (TRUE, FALSE, UNKNOWN), `IS NULL` vs `IS NOT NULL`, `COALESCE()`, and `IFNULL()`.
NULL in SQL represents missing, unknown, or non-existent data, operating under Three-Valued Logic:
Three-Valued Logic Rules:
•
Comparing any value to 'NULL' using standard equality ('col = NULL' or 'col <> NULL') evaluates to 'UNKNOWN' (treated as FALSE in WHERE clauses!).
•
Checking NULL: Must use 'IS NULL' or 'IS NOT NULL'.
•
'COALESCE(val1, val2, ...)': Returns the first non-null argument in the parameter list.
Code Implementation Example
sql
-- Safe NULL handling with COALESCE
SELECT
id,
first_name,
COALESCE(phone_number, 'N/A') AS contact_phone
FROM users
WHERE deleted_at IS NULL;💡Replacing NULL values with fallback strings using COALESCE.
🎯 Interview Key Takeaway
Never use = NULL to check for missing data; always use IS NULL or COALESCE to handle NULL values cleanly.
#SQL#NULL#COALESCE#Three-Valued Logic#Beginner
Back to all SQL & MySQL Questions→