What you will be able to do
- Predict what comparison and logical operators return when one or both operands are NULL
- Write WHERE filters that keep or remove rows with missing values on purpose rather than by accident
- Replace missing values with COALESCE, NVL or IFNULL, and turn sentinel values into NULL with NULLIF
- Explain how aggregate functions, including COUNT(*), treat NULL values
Key concept
Three-valued logic in filters — In Databricks SQL a condition can be True, False or Unknown (NULL). WHERE, HAVING and JOIN keep a row only when the condition is True, so a row whose value is unknown drops out without any error unless you handle NULL explicitly.
1.Why NULL breaks ordinary comparisons
Cleaning data in SQL usually starts with missing values. In Databricks SQL, a column value that is not known when the row is created is stored as NULL. NULL is not zero and it is not an empty string. It means "unknown", and SQL treats it that way in every expression. To clean a Unity Catalog table, you first need to know how NULL behaves, because most mistakes come from comparing NULL as if it were an ordinary value.
The standard operators >, >=, =, < and <= all return NULL if either side is NULL. That is why 5 > null and null = null both evaluate to null. If you really do need to compare two values that might both be missing, Databricks provides the null-safe equal operator <=>. It returns False when exactly one side is NULL and True when both sides are NULL. Logical operators also follow three-valued rules: true OR null is true, while null OR false is null and NOT(null) is null.
| Left operand | Right operand | = (and >, >=, <, <=) | <=> |
|---|---|---|---|
| NULL | Any value | NULL | False |
| Any value | NULL | NULL | False |
| NULL | NULL | NULL | True |
Most other functions behave the same way. The documentation calls them null intolerant: give them a NULL argument and they return NULL. concat('John', null), positive(null) and to_date(null) all return null. In practice, one missing field can quietly turn a derived column into NULL as well.
Checkpoint 1 of 5· Check yourself
A derived column is built as concat(first_name, ' ', last_name). For rows where last_name is NULL, what does the derived column contain?
concat is null intolerant, so one NULL argument makes the whole result NULL. The documentation's example concat('John', null) returns null.
“Null intolerant expressions return NULL when one or more arguments of expression are NULL and most of the expressions fall in this category.”Source: docs.databricks.com
Sources1
2.Finding and filtering missing values with WHERE
Three-valued logic matters most in filters. WHERE, HAVING and JOIN keep a row only when the condition is True. A condition that evaluates to NULL counts as not satisfied, so the row disappears without any warning. The Databricks documentation uses a person table with seven rows to show this. Two of the rows, Marry and Albert, have a NULL age.
SELECT * FROM person WHERE age > 0;That query returns five rows. Marry and Albert are missing, even though nobody asked for people with a missing age to be excluded. Sometimes that is exactly the cleaning you want. If it is, write age IS NOT NULL explicitly so the next reader can see the intent. If you want to keep the unknowns, you have to ask for them with IS NULL. Writing age = NULL does not work, because that comparison is itself NULL for every row. The same rule applies to joins. A join on p1.age = p2.age drops the NULL-age people, and a join on p1.age <=> p2.age keeps them.
Checkpoint 2 of 5· Fill the gap
Complete the filter so that it also returns the people whose age is unknown.
SELECT * FROM person WHERE age > 0 OR age ? NULL;Only IS NULL (or the null-safe <=>) tests for missing values. age = NULL evaluates to NULL for every row, so the disjunction would add nothing.
Sources1
3.Replacing missing values with COALESCE, NVL and NULLIF
Filtering removes rows. Often you want to keep the row and fill in the gap instead. Databricks SQL has a group of expressions designed to work with NULL operands, including COALESCE, NULLIF, IFNULL, NVL, NVL2, ISNULL and ISNOTNULL. The workhorse is coalesce, which returns the first non-null argument. coalesce(discount, 0) is the usual way to say "use the stored value, or 0 if it is missing". The result type is the least common type of the arguments, so the replacement value must share a common type with the column.
It does not. Most functions evaluate every argument before they run. coalesce instead evaluates its arguments left to right and stops at the first non-null value. coalesce(2, 5 / 0) returns 2 and never touches the division. coalesce(NULL, 5 / 0) does evaluate the second argument and fails. If every argument is NULL, the result is still NULL, so put a literal last if you need a guaranteed value.
| Function | Returns | Example from the docs |
|---|---|---|
| coalesce(expr1 [, ...]) | The first non-null argument; NULL if all are NULL | coalesce(null, null, 3, null) → 3 |
| nvl(expr1, expr2) | expr2 if expr1 is NULL, otherwise expr1 (synonym for two-argument coalesce) | nvl(NULL, 2) → 2 |
| nullif(expr1, expr2) | NULL if expr1 equals expr2, otherwise expr1 | nullif(2, 2) → NULL |
| isnull(expr) | true on null input, false otherwise | isnull(null) → true |
NULLIF works in the opposite direction. It turns a specific value into NULL. That is useful when a source system writes a sentinel such as an empty string or -1 where it meant "unknown". After the sentinel becomes NULL, every other NULL-aware tool works on it, including COALESCE, IS NULL and aggregates.
Checkpoint 3 of 5· Match them up
Match each expression to what it returns.
Tap a term, then the definition that fits it.
coalesce and nvl return the first non-null value, nullif only returns NULL when its two arguments are equal, and coalesce returns NULL when every operand is NULL.
“coalesce evaluates arguments left to right until a non-null value is found. If all arguments are NULL, the result is NULL.”Source: docs.databricks.com
Checkpoint 4 of 5· Exam question
A Unity Catalog table `sales.orders` has an `order_total` column where some rows hold `NULL` because the value was never captured, and other rows hold `-1` as a placeholder inserted by a legacy loader. An analyst wants a query that reports 0 for both cases without altering the underlying table. Which SQL statement produces the correct result?
Correct answer: A — SELECT CASE WHEN order_total IS NULL OR order_total = -1 THEN 0 ELSE order_total END FROM sales.orders
- A. A CASE expression can test both conditions explicitly, mapping a true NULL and the -1 placeholder to 0 while leaving every other value unchanged, which is exactly what the analyst needs without modifying the stored rows.
- B. COALESCE only substitutes when an argument is NULL, so it would replace NULL with -1 (the second argument) rather than 0, and it never touches rows that already contain -1, producing the wrong output for both cases.
- C. Filtering rows out with WHERE removes the problem rows from the result set entirely instead of reporting them as 0, so the row count and totals would no longer reflect all orders.
- D. NULLIF converts -1 into NULL rather than into 0, and it leaves genuinely NULL rows unchanged, so neither placeholder case ends up reported as 0.
4.How missing values change aggregate results
Before and after cleaning, you will usually profile a column with counts and averages. Aggregates have their own NULL rule. Every aggregate function ignores NULL values except COUNT(*). On the person table, count(*) returns 7 but count(age) returns 5. The difference between them is the number of missing ages, which makes the pair a quick way to measure how much cleaning a column needs. max(age) is 50 because the two NULLs are skipped, not treated as zero.
Empty input behaves differently. MAX, MIN, SUM, AVG, EVERY, ANY and SOME return NULL when every input value is NULL or the input set is empty. count(*) on an empty set returns 0. If a dashboard must show 0 instead of a blank, wrap the aggregate in coalesce.
Checkpoint 5 of 5· Check yourself
A table has 7 rows and 2 of them have NULL in age. What do count(*) and count(age) return?
count(*) counts every row. count(age), like every other aggregate, skips NULL values, so it counts only the 5 known ages.
“NULL values are ignored from processing by all the aggregate functions. Only exception to this rule is COUNT(*) function.”Source: docs.databricks.com
The WHERE clause is never true, so the input set is empty. MAX is one of the aggregates that return NULL on empty input, and COUNT(*) returns 0.
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.
WHERE col = NULLfinds rows with missing values, andWHERE age > 0keeps rows where age is unknown.Why is that wrong?Any ordinary comparison with NULL evaluates to NULL, and WHERE keeps only rows whose condition is True. Use IS NULL / IS NOT NULL or the null-safe <=> operator.
2.coalesce evaluates all of its arguments, so an expression that would error anywhere in the list always makes the query fail.Why is that wrong?
coalesce short-circuits. It evaluates left to right and stops at the first non-null value, so coalesce(2, 5 / 0) returns 2 without error.
Covered in Replacing missing values with COALESCE, NVL and NULLIF
3.count(column) and count(*) return the same number.Why is that wrong?
Aggregates skip NULL values, with COUNT(*) as the only exception, so count(column) is lower whenever the column has missing values.
Covered in How missing values change aggregate results
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Databricks provides a null-safe equal operator (<=>), which returns False when one of the operand is NULL”
↩︎ Why NULL breaks ordinary comparisons“The result of these operators is unknown or NULL when one of the operands or both the operands are unknown or NULL.”
↩︎ Why NULL breaks ordinary comparisons“`IS NULL` expression is used in disjunction to select the persons”
↩︎ Finding and filtering missing values with WHERE“Some aggregate functions return NULL when all input values are NULL or the input data set is empty.”
↩︎ How missing values change aggregate results“a condition expression is a boolean expression and can return True, False or Unknown (NULL)”
↩︎ Key concept“Persons whose age is unknown (`NULL`) are filtered out from the result set.”
↩︎ Exam trap 1“NULL values are ignored from processing by all the aggregate functions. Only exception to this rule is COUNT(*) function.”
↩︎ Exam trap 3“Null intolerant expressions return NULL when one or more arguments of expression are NULL and most of the expressions fall in this category.”
↩︎ Checkpoint“NULL values are ignored from processing by all the aggregate functions. Only exception to this rule is COUNT(*) function.”
↩︎ Checkpoint - 2.
“The result type is the least common type of the arguments.”
↩︎ Replacing missing values with COALESCE, NVL and NULLIF“coalesce evaluates arguments left to right until a non-null value is found.”
↩︎ Exam trap 2“coalesce evaluates arguments left to right until a non-null value is found. If all arguments are NULL, the result is NULL.”
↩︎ Checkpoint - 3.
“This function is a synonym for coalesce(expr1, expr2) with two arguments.”
↩︎ Replacing missing values with COALESCE, NVL and NULLIF - 4.
“Returns NULL if expr1 equals expr2, or expr1 otherwise.”
↩︎ Replacing missing values with COALESCE, NVL and NULLIF