Q8: How does SQL evaluate NULL values and Three-Valued Logic?
NULL in SQL represents an unknown or missing value, not zero or an empty string ('').
Because NULL is unknown, direct equality comparisons (field = NULL or NULL = NULL) evaluate to UNKNOWN, not TRUE or FALSE. SQL utilizes Three-Valued Logic (TRUE, FALSE, UNKNOWN).
To check for missing values, use IS NULL or IS NOT NULL. Functions like COALESCE(col, fallback) or NVL() replace NULL values with predefined defaults.
Handling NULL safely with COALESCE
-- Incorrect: evaluates to UNKNOWN for nulls
SELECT * FROM employees WHERE commission_pct = NULL;
-- Correct: handles null check explicitly
SELECT * FROM employees WHERE commission_pct IS NULL;
-- Replacing null values in calculations
SELECT employee_id, salary + COALESCE(commission_pct, 0) AS total_comp
FROM employees;💡 COALESCE returns the first non-null argument, guaranteeing arithmetic operations do not resolve to NULL.
Key Takeaways
- Arithmetic operations involving NULL return NULL (
100 + NULL = NULL). - Always use
IS NULLorCOALESCE()when comparing columns that permit nullability.