Snowflake SQL Tutorial 9 - DISTINCT Is NOT a Function - Why COUNT(DISTINCT) Gave Me a Different Answer

Snowflake SQL · Episode 9

In this post we are going to cover DISTINCT in Snowflake.

DISTINCT removes duplicates from a result. We write it in front of the column list, and instead of one row per row in the table, Snowflake gives us back one row for each different set of values it finds.

We will start with some sample data, then de-duplicate one column, then two columns, and then look at what happened to the NULL along the way. At the end we will count that same column three different ways and get three different answers out of it.

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

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

On this page

1. Let's create some sample data
2. Let's start with one column and its duplicates
3. Now let's look at that NULL
4. Now let's select two columns instead of one
5. What about ALL?
6. Now let's count that same column three ways
7. What will we actually use DISTINCT for?

Let's create some sample data

Everything on this page runs against one small table called EMPLOYEES. The script below creates it and fills it, so you can paste it into a Snowflake worksheet and run every query here yourself. If you followed the earlier posts you already have the table.

Sample data

-- Creates the EMPLOYEES table used by every episode in this series.
-- Run it once; every later episode can reuse it.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

One column does nearly all the work in this post, and that is DEPT_ID. It has repeats in it, which is what makes it worth de-duplicating. It also has a NULL in it, because Grace has no department, and that is what makes the last part of this page interesting.

The one idea to hold on to

DISTINCT works on whatever you select. Change the column list and you change the answer, which is the trap this whole page is built around.

Let's start with one column and its duplicates

Let's pull the department column out of the table on its own, with nothing done to it.

Query

SELECT DEPT_ID FROM EMPLOYEES;

Result8 rows · 1 column

DEPT_ID
10
10
20
20
30
10
30
NULL

One row per employee, and the repeats are easy to see. 10 three times, 20 twice, 30 twice, and one NULL.

Now let's write DISTINCT in front of the column list and run the same query again.

Query

SELECT DISTINCT DEPT_ID FROM EMPLOYEES;

Result4 rows · 1 column

DEPT_ID
NULL
20
10
30

Four rows instead of eight. The duplicates are gone. The documentation describes what just happened in one sentence: “DISTINCT eliminates duplicate values from the result set.”

Now let's look at the order those four came back in

NULL, 20, 10, 30. That is not sorted. Nothing on this page has been reordered. That is what the account returned for that query on that run.

Watch out

DISTINCT de-duplicates. It does not sort. Small results often come back looking tidy by accident, which is exactly how people end up believing otherwise and then shipping a report that depends on it. If you want an order, ask for one.

Asking for one means ORDER BY, which we covered in episode 7. Let's add it, and notice that it changes nothing about which rows we get.

Query

SELECT DISTINCT DEPT_ID FROM EMPLOYEES ORDER BY DEPT_ID;

Result4 rows · 1 column

DEPT_ID
10
20
30
NULL

The same four values. Now they are in an order we asked for, rather than an order we were handed.

Now let's look at that NULL

Stop on that last grid for a second, because there is something odd in it. The NULL survived DISTINCT, and there is exactly one of it.

That should bother us if we remember episode 6. NULL = NULL does not come out as TRUE. It comes out as NULL. So how did DISTINCT decide that one empty department was a duplicate of another empty department?

Because DISTINCT is not using the equals operator at all. It is de-duplicating rows. As far as that job is concerned, two rows that are both empty in the same place are the same row.

Observation, not a quoted rule

Being straight with you about where that comes from. The SELECT documentation page says only: “ALL includes all values in the result set. DISTINCT eliminates duplicate values from the result set.” That is the whole of it. It does not say what happens to NULLs.
So the paragraph above is not a quoted rule — it is an observation from a real account, which is better than a guess and worth knowing the difference.

Now let's select two columns instead of one

This is the part of the post worth taking away. Same DISTINCT, same table, but two columns in the select list instead of one.

Query

SELECT DISTINCT DEPT_ID, MANAGER_ID FROM EMPLOYEES ORDER BY DEPT_ID, MANAGER_ID;

Result7 rows · 2 columns

DEPT_ID	MANAGER_ID
10	6
10	NULL
20	3
20	6
30	5
30	6
NULL	6

Seven rows. Let's think about what that number means.

If DISTINCT operated on DEPT_ID, the way a function operates on its argument, then DEPT_ID would have collapsed to the four values we saw above. It clearly has not. 10 appears twice in that grid and 30 appears twice.

What actually happened is that Snowflake looked at each row as a whole pair and dropped the pairs that were identical to a pair it had already kept. Eight employees, seven different department-and-manager combinations. So one pair was a duplicate and one row went.

Now let's put brackets around the column

People write the next query all the time. It looks like a function call, so we would expect it to de-duplicate that one column and leave the other one alone.

Query — the parentheses change nothing

SELECT DISTINCT(DEPT_ID), MANAGER_ID FROM EMPLOYEES ORDER BY DEPT_ID, MANAGER_ID;

Result7 rows · 2 columns

DEPT_ID	MANAGER_ID
10	6
10	NULL
20	3
20	6
30	5
30	6
NULL	6

Seven rows again, identical to the grid above it. The brackets did nothing at all. They are ordinary grouping brackets around an expression, the same ones we would put around SALARY * 12, and they do not scope DISTINCT to anything.

The trap this episode is named after

There is no syntax that makes DISTINCT apply to one column of a select list and not the rest. DISTINCT is a set quantifier on the whole result, not a function with an argument. If you need distinct values of one column only, select only that column — or reach for GROUP BY, which arrives in Level 2.

What you write	What it de-duplicates
SELECT DISTINCT DEPT_ID	the single-column rows — one row per distinct department
SELECT DISTINCT DEPT_ID, MANAGER_ID	the whole pair — one row per distinct combination
SELECT DISTINCT(DEPT_ID), MANAGER_ID	the whole pair, exactly as above — the brackets are decoration

What about ALL?

DISTINCT has an opposite, and it is a keyword almost nobody writes, because we get it for free. Let's write it out once to see what it does.

Query

SELECT ALL DEPT_ID FROM EMPLOYEES;

Result8 rows · 1 column

DEPT_ID
10
10
20
20
30
10
30
NULL

Every row, duplicates included, identical to the very first grid on this page. From the documentation: “ALL includes all values in the result set.” It is the default.

Which means every SELECT we have ever written already had an invisible ALL in it. Writing it out is legal, and now and then it is useful for emphasis in a query somebody else will read. Otherwise there is no reason to type it.

Now let's count that same column three ways

This is the last one, and it is the one that quietly puts wrong numbers into real reports. We have already seen that SELECT DISTINCT DEPT_ID returns four rows. Now let's count that same column three different ways, side by side.

Query

SELECT COUNT(DEPT_ID) AS COUNT_COL, COUNT(DISTINCT DEPT_ID) AS COUNT_DISTINCT, COUNT(*) AS COUNT_STAR FROM EMPLOYEES;

Result1 row · 3 columns

COUNT_COL	COUNT_DISTINCT	COUNT_STAR
7	3	8

Start with the two easy ones. COUNT(*) is 8, because there are eight rows in the table. COUNT(DEPT_ID) is 7, because COUNT of a column ignores NULLs, so Grace is not counted. Both of those were episode 6.

Now the middle column. COUNT(DISTINCT DEPT_ID) is 3. And SELECT DISTINCT DEPT_ID gave us four rows two sections ago, on this same column, in this same table.

The difference is the NULL

SELECT DISTINCT kept it, because a row with nothing in it is still a row. COUNT threw it away before it counted anything. Its documentation says it “excludes rows with NULL values”, and putting DISTINCT inside the COUNT does not change that.

Expression	Answer	Why
COUNT(*)	8	every row, nothing inspected
COUNT(DEPT_ID)	7	non-NULL values, duplicates included
COUNT(DISTINCT DEPT_ID)	3	non-NULL values, de-duplicated
SELECT DISTINCT DEPT_ID	4 rows	de-duplicated rows, NULL kept as a row

The quiet one

The same keyword hands you one answer in a SELECT list and a different one inside a COUNT, with no error and no warning. If somebody asks how many departments there are, both numbers are defensible — and you had better know which one you just gave them.

What will we actually use DISTINCT for?

Traps aside, there is one everyday job DISTINCT does better than anything else. It answers the question what values are really in this column? The next two queries run against ORDERS and CUSTOMERS, two other tables in the same demo database.

Query

SELECT DISTINCT STATUS FROM ORDERS ORDER BY STATUS;

Result4 rows · 1 column

STATUS
CANCELLED
PENDING
RETURNED
SHIPPED

That is the complete set of statuses in that column. It is faster than reading a schema, and it tells us what is actually there rather than what somebody intended to be there.

Query

SELECT DISTINCT COUNTRY FROM CUSTOMERS ORDER BY COUNTRY;

Result4 rows · 1 column

COUNTRY
Canada
UK
USA
NULL

Three countries, and a NULL, because a customer has no country recorded. That NULL is exactly the kind of thing this query is for. No table definition would have told us about it.

On that sort order

The NULL sorted last on an ascending sort here, which matches what episode 7 showed — and, as that episode said, a default is not a guarantee. Spell out NULLS FIRST or NULLS LAST when it matters.

Key points

• DISTINCT removes duplicate rows of the result, and its opposite is ALL — the default every SELECT already has.
• DISTINCT applies to the entire select list. Adding a second column changes which rows are duplicates, and therefore changes the answer.
• DISTINCT(COL), OTHER is not a function call. Those are grouping parentheses, and the result is identical to writing no parentheses at all.
• DISTINCT keeps exactly one NULL, even though NULL = NULL is not TRUE. The documentation does not state this — it is an observation from a real account.
• DISTINCT does not sort. The grid on this page came back NULL, 20, 10, 30; add ORDER BY if the order matters.
• COUNT(DEPT_ID) is 7, COUNT(DISTINCT DEPT_ID) is 3, COUNT(*) is 8 — three different answers about one column.
• SELECT DISTINCT counts the NULL as a row; COUNT(DISTINCT ...) discards it first. That is the entire difference between the two answers.
• Pointed at one column of an unfamiliar table, SELECT DISTINCT tells you what is really in it — including the NULLs no schema mentions.

Next in this series

In the next post we will look at column aliases and AS, including the missing comma that silently turns a column into an alias, and exactly where Snowflake lets us reuse an alias, which is further than most databases do.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above. The EMPLOYEES data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Clause descriptions are quoted from the Snowflake SELECT documentation and the COUNT documentation.