Snowflake SQL Tutorial 80 - IFF, NVL, NVL2, COALESCE, NULLIF, DECODE - Null Handling

Snowflake SQL · Episode 80

In this post we are going to cover the null handling functions in Snowflake: IFF, NVL, NVL2, COALESCE, NULLIF and DECODE.

They all do roughly the same kind of job. Each one looks at a value, decides whether it is there or not, and gives you something back. The differences between them are small, and every one of those differences is a decision about what a missing value means.

We will start with IFF and build it up step by step. On the way we will write a column that makes a false statement about five people, divide by zero and survive it, and find out which one of these functions can match a NULL when the simple CASE form cannot.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 80 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's see what one null does to a calculation
3. Let's start with IFF
4. Now let's see what IFF does with a NULL condition
5. Is IFF lazy?
6. Now let's replace a null with NVL
7. NVL2 takes three arguments, and people misread it
8. But what did replacing null with zero actually change?
9. Now let's use COALESCE for as many arguments as we like
10. Is COALESCE lazy too?
11. Now the inverse: NULLIF
12. Let's divide by a computed value and watch it fail
13. Two more things NULLIF is good for
14. Can anything match a NULL? DECODE can
15. One last thing: how many ways can we write the same fix?

Let's create some sample data

This post runs against the EMPLOYEES table that the rest of the course uses, plus CUSTOMERS and a deliberately messy SALES_RAW later on. The script below just points the worksheet at them.

Copy it into a Snowflake worksheet and run it once.

Sample data

-- EMPLOYEES, CUSTOMERS and SALES_RAW come from the setup script this
-- course uses throughout. Run it once; every episode reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

Here is the whole employees table.

Query

SELECT EMP_ID, FIRST_NAME, LAST_NAME, DEPT_ID, MANAGER_ID, SALARY,
       COMMISSION_PCT, HIRE_DATE
FROM EMPLOYEES ORDER BY EMP_ID;

Result8 rows · 8 columns · scroll →

EMP_ID	FIRST_NAME	LAST_NAME	DEPT_ID	MANAGER_ID	SALARY	COMMISSION_PCT	HIRE_DATE
1	Aisha	Khan	10	6	118000.00	NULL	2019-03-11
2	Marcus	Reid	10	6	96500.00	NULL	2021-07-01
3	Lena	Fischer	20	6	104000.00	0.05	2018-01-22
4	Tomas	Novak	20	3	87250.00	NULL	2022-09-05
5	Priya	Raman	30	6	96500.00	0.10	2020-11-17
6	Daniel	O'Connor	10	NULL	131000.00	NULL	2016-05-30
7	Sofia	Marino	30	5	71400.00	0.08	2023-02-13
8	Grace	Lin	NULL	6	92000.00	NULL	2021-04-19

Have a look at the COMMISSION_PCT column. It is a rate, so 0.05 means five per cent. Some rows have one and some rows say NULL.

Let's count those properly rather than reading them off the screen.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(COMMISSION_PCT) AS HAVE_A_RATE,
       COUNT(*) - COUNT(COMMISSION_PCT) AS RATE_IS_NULL,
       COUNT(MANAGER_ID) AS HAVE_A_MANAGER,
       COUNT(*) - COUNT(MANAGER_ID) AS MANAGER_IS_NULL,
       COUNT(DEPT_ID) AS HAVE_A_DEPARTMENT
FROM EMPLOYEES;

Result1 row · 6 columns · scroll →

ROWS_TOTAL	HAVE_A_RATE	RATE_IS_NULL	HAVE_A_MANAGER	MANAGER_IS_NULL	HAVE_A_DEPARTMENT
8	3	5	7	1	7

8 rows. 3 of them have a commission rate and 5 do not. 7 have a manager, 1 does not, and 7 have a department.

So the missing rate is not a rare edge case in this table. It is the majority of it, and that is why this post exists.

Let's see what one null does to a calculation

Before any of the functions, here is why the nulls matter immediately. Let's work out each person's commission: salary times the rate.

Query

SELECT EMP_ID, FIRST_NAME, SALARY, COMMISSION_PCT,
       SALARY * COMMISSION_PCT AS COMMISSION_AMOUNT
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 5 columns

EMP_ID	FIRST_NAME	SALARY	COMMISSION_PCT	COMMISSION_AMOUNT
1	Aisha	118000.00	NULL	NULL
2	Marcus	96500.00	NULL	NULL
3	Lena	104000.00	0.05	5200.0000
4	Tomas	87250.00	NULL	NULL
5	Priya	96500.00	0.10	9650.0000
6	Daniel	131000.00	NULL	NULL
7	Sofia	71400.00	0.08	5712.0000
8	Grace	92000.00	NULL	NULL

3 numbers and 5 nulls. Any arithmetic with a NULL gives back NULL, so the 5 rows with no rate have no commission amount either.

We have not done anything clever yet and most of the column is already gone.

Let's start with IFF

IFF takes three things: a condition, a value to use if it is true, and a value to use if it is not. That is the whole function.

Let's split the salaries into six figures and below, and put the equivalent CASE next to it so we can see they are the same thing.

Query

SELECT EMP_ID, FIRST_NAME, SALARY,
       IFF(SALARY >= 100000, 'Six figures', 'Below') AS BAND,
       CASE WHEN SALARY >= 100000 THEN 'Six figures' ELSE 'Below' END
         AS CASE_FOR_COMPARISON
FROM EMPLOYEES
ORDER BY SALARY DESC;

Result8 rows · 5 columns

EMP_ID	FIRST_NAME	SALARY	BAND	CASE_FOR_COMPARISON
6	Daniel	131000.00	Six figures	Six figures
1	Aisha	118000.00	Six figures	Six figures
3	Lena	104000.00	Six figures	Six figures
2	Marcus	96500.00	Below	Below
5	Priya	96500.00	Below	Below
8	Grace	92000.00	Below	Below
4	Tomas	87250.00	Below	Below
7	Sofia	71400.00	Below	Below

The two columns are identical all the way down. Let's count that rather than trust it.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(IFF(SALARY >= 100000, 'Six figures', 'Below')
                = CASE WHEN SALARY >= 100000 THEN 'Six figures'
                       ELSE 'Below' END) AS AGREE
FROM EMPLOYEES;

Result1 row · 2 columns

ROWS_TOTAL	AGREE
8	8

8 rows, and the two expressions agree on 8 of them.

So IFF is a shorter CASE with exactly two branches. Use it when you have two. Reach for CASE the moment you have three.

Now let's see what IFF does with a NULL condition

Here is the query that this whole post is really about. Does this person earn commission? The condition is COMMISSION_PCT > 0, and the two answers are 'Earns commission' and 'Does not'.

That reads like a perfectly sensible column. Let's run it, with the condition on its own beside it, and a properly written version in the last column.

Query

SELECT EMP_ID, COMMISSION_PCT,
       IFF(COMMISSION_PCT > 0, 'Earns commission', 'Does not') AS THE_ANSWER,
       COMMISSION_PCT > 0                                      AS THE_CONDITION_ITSELF,
       IFF(COMMISSION_PCT IS NULL, 'No rate recorded',
           IFF(COMMISSION_PCT > 0, 'Earns commission', 'Zero')) AS WRITTEN_PROPERLY
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 5 columns

EMP_ID	COMMISSION_PCT	THE_ANSWER	THE_CONDITION_ITSELF	WRITTEN_PROPERLY
1	NULL	Does not	NULL	No rate recorded
2	NULL	Does not	NULL	No rate recorded
3	0.05	Earns commission	TRUE	Earns commission
4	NULL	Does not	NULL	No rate recorded
5	0.10	Earns commission	TRUE	Earns commission
6	NULL	Does not	NULL	No rate recorded
7	0.08	Earns commission	TRUE	Earns commission
8	NULL	Does not	NULL	No rate recorded

Look at THE_ANSWER and THE_CONDITION_ITSELF together. On the rows with no rate, the condition is not FALSE. It is NULL.

NULL is not true, so IFF took the false branch. That is the documented behaviour and it is working exactly as designed.

The problem is what the column now says. 'Does not' is a claim about a person, and the truth is that we do not know. There is no rate recorded for them.

Let's get the number on screen.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(COMMISSION_PCT IS NULL) AS ACTUALLY_NULL,
       COUNT_IF(IFF(COMMISSION_PCT > 0, 'Earns commission', 'Does not') = 'Does not')
         AS LABELLED_DOES_NOT,
       COUNT_IF(COMMISSION_PCT IS NULL
                AND IFF(COMMISSION_PCT > 0, 'Earns commission', 'Does not') = 'Does not')
         AS NULLS_HIDDEN_IN_DOES_NOT
FROM EMPLOYEES;

Result1 row · 4 columns

ROWS_TOTAL	ACTUALLY_NULL	LABELLED_DOES_NOT	NULLS_HIDDEN_IN_DOES_NOT
8	5	5	5

8 rows. 5 are actually NULL. 5 were labelled 'Does not'. And 5 nulls are hidden inside that label.

Every single unknown became a confident no, and nothing in the result says so.

The fix is in the last column of the grid above. Test IS NULL first, in its own branch, and you get 'No rate recorded', 'Earns commission' and 'Zero'. Three states, because there genuinely are three.

Is IFF lazy?

Worth knowing, because if the unused branch is never evaluated you can afford to put something expensive in it.

Let's put a division by zero in the branch that should not run, once on each side.

Query

SELECT IFF(TRUE, 1, 1/0)  AS FALSE_BRANCH_IS_BAD,
       IFF(FALSE, 1/0, 1) AS TRUE_BRANCH_IS_BAD;

Result1 row · 2 columns

FALSE_BRANCH_IS_BAD	TRUE_BRANCH_IS_BAD
1.000000	1.000000

Both columns came back as 1.000000. Neither division by zero ever happened, so the unused branch really is skipped.

Now let's replace a null with NVL

NVL takes two arguments. If the first one is NULL, you get the second one. Otherwise you get the first.

IFNULL is the same function with a different name. Let's put them side by side and compare them on every row.

Query

SELECT EMP_ID, COMMISSION_PCT,
       IFNULL(COMMISSION_PCT, 0) AS VIA_IFNULL,
       NVL(COMMISSION_PCT, 0)    AS VIA_NVL,
       IFNULL(COMMISSION_PCT, 0) = NVL(COMMISSION_PCT, 0) AS SAME
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 5 columns

EMP_ID	COMMISSION_PCT	VIA_IFNULL	VIA_NVL	SAME
1	NULL	0.00	0.00	TRUE
2	NULL	0.00	0.00	TRUE
3	0.05	0.05	0.05	TRUE
4	NULL	0.00	0.00	TRUE
5	0.10	0.10	0.10	TRUE
6	NULL	0.00	0.00	TRUE
7	0.08	0.08	0.08	TRUE
8	NULL	0.00	0.00	TRUE

The SAME column is TRUE all the way down. They are interchangeable, so use whichever one your team already uses and stop thinking about it.

NVL2 takes three arguments, and people misread it

NVL2 is the one that catches people out. It does not mean if null. It means: if the first argument is not null give me the second, otherwise give me the third.

So the second argument is the not-null case. Let's run it with an IFF beside it that spells out the same logic.

Query

SELECT EMP_ID, COMMISSION_PCT,
       NVL2(COMMISSION_PCT, 'Has a rate', 'Does not')          AS NVL2_,
       IFF(COMMISSION_PCT IS NOT NULL, 'Has a rate', 'Does not') AS IFF_FOR_COMPARISON,
       NVL2(COMMISSION_PCT, SALARY * COMMISSION_PCT, 0)         AS COMMISSION_OR_ZERO
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 5 columns

EMP_ID	COMMISSION_PCT	NVL2_	IFF_FOR_COMPARISON	COMMISSION_OR_ZERO
1	NULL	Does not	Does not	0.0000
2	NULL	Does not	Does not	0.0000
3	0.05	Has a rate	Has a rate	5200.0000
4	NULL	Does not	Does not	0.0000
5	0.10	Has a rate	Has a rate	9650.0000
6	NULL	Does not	Does not	0.0000
7	0.08	Has a rate	Has a rate	5712.0000
8	NULL	Does not	Does not	0.0000

The two label columns match, and the last column is the useful shape: the commission where there is a rate, and zero where there is not.

Let's count the agreement.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(NVL2(COMMISSION_PCT, 'Has a rate', 'Does not')
                = IFF(COMMISSION_PCT IS NOT NULL, 'Has a rate', 'Does not')) AS AGREE
FROM EMPLOYEES;

Result1 row · 2 columns

ROWS_TOTAL	AGREE
8	8

8 of 8. So NVL2(x, a, b) is IFF(x IS NOT NULL, a, b), and the second argument is the one that goes with a value being present.

It reads nicely on the manager column too.

Query

SELECT EMP_ID, FIRST_NAME, MANAGER_ID,
       NVL2(MANAGER_ID, 'Reports to someone', 'Top of the tree') AS REPORTING
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 4 columns

EMP_ID	FIRST_NAME	MANAGER_ID	REPORTING
1	Aisha	6	Reports to someone
2	Marcus	6	Reports to someone
3	Lena	6	Reports to someone
4	Tomas	3	Reports to someone
5	Priya	6	Reports to someone
6	Daniel	NULL	Top of the tree
7	Sofia	5	Reports to someone
8	Grace	6	Reports to someone

Daniel has no manager, so he comes back as 'Top of the tree'. That is a sentence a report can print, which a NULL is not.

But what did replacing null with zero actually change?

This is the important part of the post, and it is about what we just did rather than how to do it. We replaced NULL with zero. So what changed?

Let's average the rate two ways, and sum it two ways, on the same grid.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(COMMISSION_PCT)                        AS NON_NULL_ROWS,
       ROUND(AVG(COMMISSION_PCT), 4)                AS AVG_IGNORING_NULLS,
       ROUND(AVG(NVL(COMMISSION_PCT, 0)), 4)        AS AVG_TREATING_NULL_AS_ZERO,
       ROUND(SUM(COMMISSION_PCT), 4)                AS SUM_IGNORING_NULLS,
       ROUND(SUM(NVL(COMMISSION_PCT, 0)), 4)        AS SUM_TREATING_NULL_AS_ZERO
FROM EMPLOYEES;

Result1 row · 6 columns · scroll →

ROWS_TOTAL	NON_NULL_ROWS	AVG_IGNORING_NULLS	AVG_TREATING_NULL_AS_ZERO	SUM_IGNORING_NULLS	SUM_TREATING_NULL_AS_ZERO
8	3	0.0767	0.0288	0.23	0.23

The average ignoring the nulls is 0.0767. The average treating NULL as zero is 0.0288. That is less than half.

Now look at the two sums. 0.23 and 0.23. Identical, and of course they are — adding zero to a total changes nothing.

The whole difference is the divisor. The same 0.23 was divided by 3 rows in one case and by 8 rows in the other.

Let's see the same thing in money, which is where somebody notices.

Query

SELECT ROUND(AVG(SALARY * COMMISSION_PCT), 2)               AS AVG_OF_THOSE_WHO_EARN_IT,
       ROUND(AVG(SALARY * NVL(COMMISSION_PCT, 0)), 2)       AS AVG_ACROSS_EVERYONE,
       ROUND(SUM(SALARY * NVL(COMMISSION_PCT, 0)), 2)       AS TOTAL_COMMISSION,
       COUNT(SALARY * COMMISSION_PCT)                       AS ROWS_IN_THE_FIRST,
       COUNT(SALARY * NVL(COMMISSION_PCT, 0))               AS ROWS_IN_THE_SECOND
FROM EMPLOYEES;

Result1 row · 5 columns

AVG_OF_THOSE_WHO_EARN_IT	AVG_ACROSS_EVERYONE	TOTAL_COMMISSION	ROWS_IN_THE_FIRST	ROWS_IN_THE_SECOND
6854.00	2570.25	20562.00	3	8

Average commission of the people who earn it: 6854.00, over 3 rows. Average commission across everybody: 2570.25, over 8 rows. The total of 20562.00 is the same either way.

Both numbers are correct. They answer different questions.

The point of this section

Writing NVL(x, 0) is not tidying up. It is choosing which question your number answers. Decide which one you meant, and then write the one you meant.

Now let's use COALESCE for as many arguments as we like

COALESCE takes any number of arguments and returns the first one that is not NULL. Let's use the customers table for this, because it has a missing country.

Query

SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY, COUNTRY, SEGMENT
FROM CUSTOMERS ORDER BY CUSTOMER_ID;

Result10 rows · 5 columns

CUSTOMER_ID	CUSTOMER_NAME	CITY	COUNTRY	SEGMENT
101	Northwind Traders	Charlotte	USA	Enterprise
102	Blue Ridge Supply	Asheville	USA	SMB
103	Kestrel Analytics	Toronto	Canada	Enterprise
104	Harbour Foods	Vancouver	Canada	SMB
105	Alpine Logistics	Zurich	NULL	Enterprise
106	Delta Print Co	Raleigh	USA	SMB
107	Meridian Health	Boston	USA	Enterprise
108	Copper Kettle Ltd	Manchester	UK	SMB
109	Vantage Media	London	UK	Enterprise
110	Sable Interiors	Austin	USA	SMB

Customer 105, Alpine Logistics, has no country. Now let's ask for the country, then the city, then the word Unknown, in that order.

Query

SELECT CUSTOMER_ID, CITY, COUNTRY,
       COALESCE(COUNTRY, CITY, 'Unknown')            AS FIRST_NON_NULL,
       COALESCE(NULL, NULL, NULL, 'last resort')     AS ALL_BUT_THE_LAST_ARE_NULL,
       COALESCE(COUNTRY, 'Unknown')                  AS TWO_ARGUMENTS
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 6 columns · scroll →

CUSTOMER_ID	CITY	COUNTRY	FIRST_NON_NULL	ALL_BUT_THE_LAST_ARE_NULL	TWO_ARGUMENTS
101	Charlotte	USA	USA	last resort	USA
102	Asheville	USA	USA	last resort	USA
103	Toronto	Canada	Canada	last resort	Canada
104	Vancouver	Canada	Canada	last resort	Canada
105	Zurich	NULL	Zurich	last resort	Unknown
106	Raleigh	USA	USA	last resort	USA
107	Boston	USA	USA	last resort	USA
108	Manchester	UK	UK	last resort	UK
109	London	UK	UK	last resort	UK
110	Austin	USA	USA	last resort	USA

That row falls through the missing country and comes back with Zurich. The middle column shows what happens when everything but the last argument is NULL, and the last column is COALESCE with two arguments — which is exactly NVL. It just scales further.

One type note, because mixing types is allowed and does something.

Query

SELECT COALESCE(NULL, 1, 2)          AS ALL_NUMBERS,
       COALESCE(NULL, '1', '2')      AS ALL_TEXT,
       COALESCE(NULL, 1, '2')        AS MIXED_,
       TYPEOF(COALESCE(NULL, 1, '2')::VARIANT) AS MIXED_TYPE;

Result1 row · 4 columns

ALL_NUMBERS	ALL_TEXT	MIXED_	MIXED_TYPE
1	1	1	INTEGER

Mix a number and text and COALESCE picks a type and converts. TYPEOF says INTEGER. So keep your arguments the same type, or cast them yourself and know what you are getting.

Is COALESCE lazy too?

Same question we asked of IFF, same way of answering it. Let's put a division by zero in an argument that should never be needed.

Query

SELECT COALESCE(1, 1/0)          AS FIRST_ARG_IS_FINE,
       COALESCE(NULL, 1, 1/0)    AS SECOND_ARG_IS_FINE;

Result1 row · 2 columns

FIRST_ARG_IS_FINE	SECOND_ARG_IS_FINE
1.000000	1.000000

Both fine. COALESCE stops as soon as it finds something that is not NULL, and it never looks at the rest.

But lazy is not the same as safe. Let's make it reach the bad argument.

Query

SELECT COALESCE(NULL, 1/0) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100051 (22012): Division by zero

Division by zero. The laziness protects you only while there is something earlier to return. The moment COALESCE has to evaluate that argument, it evaluates it.

Now the inverse: NULLIF

NULLIF goes the other way. It takes two values, and if they are equal it gives you NULL. Otherwise it gives you the first one.

Query

SELECT NULLIF(5, 5)      AS EQUAL_GIVES_NULL,
       NULLIF(5, 3)      AS DIFFERENT_GIVES_THE_FIRST,
       NULLIF('a', 'a')  AS TEXT_EQUAL,
       NULLIF(NULL, 5)   AS FIRST_IS_NULL,
       NULLIF(5, NULL)   AS SECOND_IS_NULL;

Result1 row · 5 columns

EQUAL_GIVES_NULL	DIFFERENT_GIVES_THE_FIRST	TEXT_EQUAL	FIRST_IS_NULL	SECOND_IS_NULL
NULL	5	NULL	NULL	5

NULLIF(5, 5) is NULL. NULLIF(5, 3) is 5. NULLIF('a', 'a') is NULL, so it works on text. NULLIF(NULL, 5) is NULL, and NULLIF(5, NULL) is 5.

Turning a value into nothing sounds like a strange thing to want. It is, until you divide.

Let's divide by a computed value and watch it fail

Here is a real calculation. How much salary is there per dollar of commission? That means dividing the salary by the commission amount, and we already made the commission amount safe with NVL earlier.

Query

SELECT EMP_ID, SALARY,
       SALARY / (SALARY * NVL(COMMISSION_PCT, 0)) AS SHOULD_NOT_GET_THIS_FAR
FROM EMPLOYEES;

Query failed — Snowflake returned an error

100051 (22012): Division by zero

Division by zero. The NVL turned the missing rates into zeros, and then we divided by them.

And this is a hard error. Not a NULL in one cell — the whole query is gone, because of rows we were not even interested in.

So let's wrap the divisor in NULLIF against zero and run it again.

Query

SELECT EMP_ID, SALARY, COMMISSION_PCT,
       SALARY * NVL(COMMISSION_PCT, 0) AS COMMISSION_AMOUNT,
       ROUND(SALARY / NULLIF(SALARY * NVL(COMMISSION_PCT, 0), 0), 2)
         AS SALARY_PER_COMMISSION_DOLLAR
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 5 columns

EMP_ID	SALARY	COMMISSION_PCT	COMMISSION_AMOUNT	SALARY_PER_COMMISSION_DOLLAR
1	118000.00	NULL	0.0000	NULL
2	96500.00	NULL	0.0000	NULL
3	104000.00	0.05	5200.0000	20.00
4	87250.00	NULL	0.0000	NULL
5	96500.00	0.10	9650.0000	10.00
6	131000.00	NULL	0.0000	NULL
7	71400.00	0.08	5712.0000	12.50
8	92000.00	NULL	0.0000	NULL

The three people who earn commission get 20.00, 10.00 and 12.50. Everybody else gets NULL, which is honest. You cannot divide by nothing, so the answer is not a number.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(SALARY / NULLIF(SALARY * NVL(COMMISSION_PCT, 0), 0)) AS RATIO_CALCULATED,
       COUNT(*) - COUNT(SALARY / NULLIF(SALARY * NVL(COMMISSION_PCT, 0), 0))
         AS RATIO_IS_NULL,
       COUNT_IF(NVL(COMMISSION_PCT, 0) = 0) AS RATE_IS_ZERO_OR_NULL
FROM EMPLOYEES;

Result1 row · 4 columns

ROWS_TOTAL	RATIO_CALCULATED	RATIO_IS_NULL	RATE_IS_ZERO_OR_NULL
8	3	5	5

8 rows, 3 ratios calculated, 5 came back NULL, and 5 rows had a rate that was zero or missing. The same 5 rows, which is not a coincidence.

The idiom worth memorising

NULLIF(divisor, 0) every time you divide by something you calculated. It turns an error that kills the statement into a NULL in the cells where there is genuinely no answer.

Two more things NULLIF is good for

Empty strings and sentinel values. Both of them are nulls that somebody wrote down as text, and NULLIF turns them back into real nulls.

Query

SELECT NULLIF('', '')              AS EMPTY_TO_NULL,
       NULLIF(TRIM('   '), '')     AS SPACES_TO_NULL,
       NULLIF('N/A', 'N/A')        AS SENTINEL_TO_NULL,
       COALESCE(NULLIF('N/A', 'N/A'), 'replaced') AS COMBINED_WITH_COALESCE;

Result1 row · 4 columns

EMPTY_TO_NULL	SPACES_TO_NULL	SENTINEL_TO_NULL	COMBINED_WITH_COALESCE
NULL	NULL	NULL	replaced

An empty string becomes NULL. A string of spaces, trimmed first, becomes NULL. And 'N/A' becomes NULL, which means COALESCE can then replace it — the last column shows those two working together.

Let's do that on a real messy table. SALES_RAW holds amounts as text, the way a spreadsheet import leaves them.

Query

SELECT ROW_ID, AMOUNT_TXT,
       NULLIF(AMOUNT_TXT, 'N/A') AS SENTINEL_REMOVED
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 3 columns

ROW_ID	AMOUNT_TXT	SENTINEL_REMOVED
1	$4,200.00	$4,200.00
2	890.50	890.50
3	$6,150	$6,150
4	2,310.75	2,310.75
5	N/A	NULL
6	$640.00	$640.00
7	9,250.00	9,250.00
8	$1,080	$1,080

Row 5 is the one. Its amount is the text N/A, and one line of NULLIF turns it into a proper NULL that the rest of your query can handle.

Can anything match a NULL? DECODE can

DECODE is an old Oracle function and it is still in Snowflake, for one good reason. You give it a column, then pairs of search value and result, then an optional default.

That makes it look exactly like the simple form of CASE, so let's write both and compare them.

Query

SELECT CUSTOMER_ID, COUNTRY,
       DECODE(COUNTRY, 'USA', 'Domestic', NULL, 'Missing', 'International')
         AS VIA_DECODE,
       CASE COUNTRY WHEN 'USA' THEN 'Domestic' WHEN NULL THEN 'Missing'
                    ELSE 'International' END AS VIA_SIMPLE_CASE
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 4 columns

CUSTOMER_ID	COUNTRY	VIA_DECODE	VIA_SIMPLE_CASE
101	USA	Domestic	Domestic
102	USA	Domestic	Domestic
103	Canada	International	International
104	Canada	International	International
105	NULL	Missing	International
106	USA	Domestic	Domestic
107	USA	Domestic	Domestic
108	UK	International	International
109	UK	International	International
110	USA	Domestic	Domestic

Find customer 105, the one with no country. DECODE says Missing. The simple CASE says International. Same row, same data, different answer.

The simple CASE form compares with =, and = NULL is never true. DECODE compares with null-safe equality, and that is the one thing it does that CASE cannot.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(*) - COUNT(COUNTRY) AS ACTUAL_NULLS,
       COUNT_IF(DECODE(COUNTRY, 'USA', 'Domestic', NULL, 'Missing', 'International')
                = 'Missing') AS DECODE_FOUND,
       COUNT_IF(CASE COUNTRY WHEN 'USA' THEN 'Domestic' WHEN NULL THEN 'Missing'
                             ELSE 'International' END = 'Missing') AS CASE_FOUND
FROM CUSTOMERS;

Result1 row · 4 columns

ROWS_TOTAL	ACTUAL_NULLS	DECODE_FOUND	CASE_FOUND
10	1	1	0

10 rows, 1 actual null. DECODE found 1. The simple CASE found 0.

One more thing about DECODE: the default is optional, and leaving it out is not the same as leaving it empty.

Query

SELECT CUSTOMER_ID, COUNTRY,
       DECODE(COUNTRY, 'USA', 'Domestic')                  AS NO_DEFAULT,
       DECODE(COUNTRY, 'USA', 'Domestic', 'Other')         AS WITH_DEFAULT
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 4 columns

CUSTOMER_ID	COUNTRY	NO_DEFAULT	WITH_DEFAULT
101	USA	Domestic	Domestic
102	USA	Domestic	Domestic
103	Canada	NULL	Other
104	Canada	NULL	Other
105	NULL	NULL	Other
106	USA	Domestic	Domestic
107	USA	Domestic	Domestic
108	UK	NULL	Other
109	UK	NULL	Other
110	USA	Domestic	Domestic

With no default, every unmatched row comes back NULL. With a default, they come back Other. Same choice you make with an ELSE, and the same consequences.

So if you are matching a list of values and one of them might be NULL, DECODE is the short way. Otherwise use the searched CASE with IS NULL in its own branch.

One last thing: how many ways can we write the same fix?

There are six spellings of replace null with zero in this post. Let's put all of them on one column and look at them together.

Query

SELECT EMP_ID, COMMISSION_PCT,
       IFNULL(COMMISSION_PCT, 0)                              AS IFNULL_,
       NVL(COMMISSION_PCT, 0)                                 AS NVL_,
       NVL2(COMMISSION_PCT, COMMISSION_PCT, 0)                AS NVL2_,
       COALESCE(COMMISSION_PCT, 0)                            AS COALESCE_,
       IFF(COMMISSION_PCT IS NULL, 0, COMMISSION_PCT)         AS IFF_,
       CASE WHEN COMMISSION_PCT IS NULL THEN 0
            ELSE COMMISSION_PCT END                           AS CASE_
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 8 columns · scroll →

EMP_ID	COMMISSION_PCT	IFNULL_	NVL_	NVL2_	COALESCE_	IFF_	CASE_
1	NULL	0.00	0.00	0.00	0.00	0.00	0.00
2	NULL	0.00	0.00	0.00	0.00	0.00	0.00
3	0.05	0.05	0.05	0.05	0.05	0.05	0.05
4	NULL	0.00	0.00	0.00	0.00	0.00	0.00
5	0.10	0.10	0.10	0.10	0.10	0.10	0.10
6	NULL	0.00	0.00	0.00	0.00	0.00	0.00
7	0.08	0.08	0.08	0.08	0.08	0.08	0.08
8	NULL	0.00	0.00	0.00	0.00	0.00	0.00

IFNULL, NVL, NVL2, COALESCE, IFF and CASE, and reading across any row they all give the same value.

Now let's check that rather than eyeball it.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(IFNULL(COMMISSION_PCT, 0) = NVL(COMMISSION_PCT, 0)
                AND NVL(COMMISSION_PCT, 0) = COALESCE(COMMISSION_PCT, 0)
                AND COALESCE(COMMISSION_PCT, 0)
                    = IFF(COMMISSION_PCT IS NULL, 0, COMMISSION_PCT)
                AND IFF(COMMISSION_PCT IS NULL, 0, COMMISSION_PCT)
                    = CASE WHEN COMMISSION_PCT IS NULL THEN 0
                           ELSE COMMISSION_PCT END)
         AS ALL_SIX_AGREE
FROM EMPLOYEES;

Result1 row · 2 columns

ROWS_TOTAL	ALL_SIX_AGREE
8	8

8 rows, and the chained comparison holds on all 8 of them.

One honest caveat about that check, because it is worth being precise. Read the query above: it chains IFNULL, NVL, COALESCE, IFF and CASE. NVL2 is not in it. So that column proves five of the six spellings identical, and the sixth is something we read off the grid above with our own eyes.

Which is the whole lesson about checks. The grid shows agreement. The check proves it, and only for what is actually written in the check.

Here is the whole family on one grid to finish with.

Query

SELECT EMP_ID, SALARY, COMMISSION_PCT, MANAGER_ID,
       NVL(COMMISSION_PCT, 0)                                  AS RATE_OR_ZERO,
       NVL2(MANAGER_ID, 'Reports to someone', 'Top of tree')    AS REPORTING,
       ROUND(SALARY + SALARY * COALESCE(COMMISSION_PCT, 0), 2)  AS TOTAL_PAY,
       ROUND(SALARY / NULLIF(SALARY * NVL(COMMISSION_PCT, 0), 0), 2) AS SAFE_RATIO,
       IFF(COMMISSION_PCT IS NULL, 'REVIEW', 'OK')              AS FLAG
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 9 columns · scroll →

EMP_ID	SALARY	COMMISSION_PCT	MANAGER_ID	RATE_OR_ZERO	REPORTING	TOTAL_PAY	SAFE_RATIO	FLAG
1	118000.00	NULL	6	0.00	Reports to someone	118000.00	NULL	REVIEW
2	96500.00	NULL	6	0.00	Reports to someone	96500.00	NULL	REVIEW
3	104000.00	0.05	6	0.05	Reports to someone	109200.00	20.00	OK
4	87250.00	NULL	3	0.00	Reports to someone	87250.00	NULL	REVIEW
5	96500.00	0.10	6	0.10	Reports to someone	106150.00	10.00	OK
6	131000.00	NULL	NULL	0.00	Top of tree	131000.00	NULL	REVIEW
7	71400.00	0.08	5	0.08	Reports to someone	77112.00	12.50	OK
8	92000.00	NULL	6	0.00	Reports to someone	92000.00	NULL	REVIEW

NVL for the rate, NVL2 for the reporting line, COALESCE inside the total pay, NULLIF keeping the ratio safe, and IFF raising a REVIEW flag on every row where the rate is missing. That last column is the one to copy. It does not guess what the missing value meant, it says that something needs looking at.

Key points

• IFF(condition, a, b) is a CASE with exactly two branches, and the two agree on every row.
• A NULL condition is not FALSE, but IFF sends it to the false branch anyway. That is how an unknown becomes a confident no.
• Test IS NULL first, in its own branch, whenever a label is a claim about something real.
• IFF never evaluates the branch it does not use.
• NVL and IFNULL are the same function with two names.
• NVL2(x, a, b) returns a when x is not null. The second argument is the not-null case.
• Replacing NULL with zero changes an average and never changes a sum, because the divisor changes and the total does not.
• COALESCE takes any number of arguments and returns the first one that is not NULL. With two arguments it is NVL.
• COALESCE short-circuits, but that only protects you while it can skip the argument. Reach it and it will evaluate it.
• NULLIF(x, y) gives NULL when the two are equal. NULLIF(divisor, 0) is how you survive dividing by a computed value.
• NULLIF also turns empty strings and 'N/A' sentinels back into real nulls.
• DECODE matches NULL, which the simple CASE form cannot, because CASE compares with =.
• There are six ways to write replace null with zero, and they produce the same value. Pick the one that reads best and be consistent.

Next in this series

Episode 81 — ZEROIFNULL, NULLIFZERO, GREATEST, LEAST and EQUAL_NULL. Including a guaranteed minimum bonus that pays nothing to exactly the people it was written for, and a column that does not equal itself.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The EMPLOYEES, CUSTOMERS and SALES_RAW data is invented demo data created by that script. The agreement check in the last section is described as covering five spellings rather than six because that is what its SQL chains; NVL2 appears in the grid above it and not in the check. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the Snowflake conditional expression functions documentation.