Welcome To TechBrothersIT: Snowflake SQL Tutorial 40

Snowflake SQL · Episode 40

In this post we are going to cover ANY, ALL and SOME in Snowflake.

All three do the same kind of work. We take one value, compare it against every value a subquery returned, and the keyword decides how many of those comparisons have to come out true for the row to qualify.

We will start with a short list of salaries and ask who beats it. Then we will show that most of what these keywords do is MIN and MAX wearing different words, that = ANY is IN, and that an empty subquery gives us two answers that are the opposite of what nearly everybody guesses.

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

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

On this page

Let's create some sample data
Let's start with > ANY
Now let's check whether > ANY is just > MIN
Now let's do the same thing with > ALL
Can we use them with other operators? = ANY is IN
Now let's try it with an empty subquery
Can a NULL in the list break > ALL? Let's look carefully
One last thing: when would we actually write these?

Let's create some sample data

Run the script below once if you do not already have these two tables. Everything on this page runs against them.

Sample data

-- Both tables already exist if you have followed this course from the start.
-- Two columns matter on this page: DEPT_ID, which picks the list every
-- comparison runs against, and COMMISSION_PCT, which is mostly NULL and comes
-- back in the last section.
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');

CREATE OR REPLACE TABLE DEPARTMENTS (
    DEPT_ID    INT,
    DEPT_NAME  VARCHAR,
    LOCATION   VARCHAR,
    BUDGET     NUMBER(12,2)
);

INSERT INTO DEPARTMENTS VALUES
    (10, 'Engineering', 'Charlotte', 2400000.00),
    (20, 'Finance',     'Charlotte', 1100000.00),
    (30, 'Marketing',   'Raleigh',    750000.00),
    (40, 'Operations',  'Raleigh',    640000.00);

Every comparison we write below runs against the salaries in department 20. There are only two of them, and that is deliberate. A short list means we can check every answer by eye.

The list, as a query

SELECT EMP_ID, FIRST_NAME, SALARY FROM EMPLOYEES WHERE DEPT_ID = 20 ORDER BY SALARY;

Result2 rows · 3 columns

EMP_ID	FIRST_NAME	SALARY
4	Tomas	87250.00
3	Lena	104000.00

Two salaries. Now let's get both ends of that list on the page as well, because every answer we get later turns out to be a comparison against one end or the other.

Query

SELECT MIN(SALARY) AS MIN_DEPT_20, MAX(SALARY) AS MAX_DEPT_20
FROM EMPLOYEES WHERE DEPT_ID = 20;

Result1 row · 2 columns

MIN_DEPT_20	MAX_DEPT_20
87250.00	104000.00

Keep those two numbers in view. Every result on this page is a comparison against one of them, even in the queries where neither MIN nor MAX is written anywhere.

Let's start with > ANY

Say the question out loud. Who earns more than any of them? There are two ways to hear that. It could mean more than any one of them we care to pick, which would be a high bar. It could mean more than at least one of them, which is a low one.

In SQL, > ANY means greater than at least one. That is where all the confusion around these keywords comes from, so it is worth having straight before we look at a single result.

Query

SELECT EMP_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEES WHERE DEPT_ID = 20)
ORDER BY SALARY;

Result6 rows · 3 columns

EMP_ID	FIRST_NAME	SALARY
8	Grace	92000.00
2	Marcus	96500.00
5	Priya	96500.00
3	Lena	104000.00
1	Aisha	118000.00
6	Daniel	131000.00

Six rows. Two employees are missing from the grid, and both are worth a look. Sofia on 71400.00 clears neither salary in the list. Tomas on 87250.00 is one of the two salaries in the list, and a value is not greater than itself, so he fails the only comparison he could have won.

The outer row and the list can overlap

Lena is in the result, and she is also in the subquery's list. Nothing excludes the rows the subquery came from. Her 104000.00 is compared against both list values just like everybody else's, and it clears the lower one.

Now let's check whether > ANY is just > MIN

If > ANY means greater than at least one of them, then beating the smallest value in the list is enough, and beating anything bigger is unnecessary. So > ANY ought to be the same query as > MIN. Let's write it that way, with no ANY anywhere in it.

Query — the same question as > MIN

SELECT EMP_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT MIN(SALARY) FROM EMPLOYEES WHERE DEPT_ID = 20)
ORDER BY SALARY;

Result6 rows · 3 columns

EMP_ID	FIRST_NAME	SALARY
8	Grace	92000.00
2	Marcus	96500.00
5	Priya	96500.00
3	Lena	104000.00
1	Aisha	118000.00
6	Daniel	131000.00

The same six employees, at the same salaries.

Now let's swap `ANY` for `SOME`

SOME is not a third behaviour to learn. It is another keyword for ANY, and the standard has both because some reads better in some sentences. Swapping one for the other should change nothing.

Query — SOME in place of ANY

SELECT EMP_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > SOME (SELECT SALARY FROM EMPLOYEES WHERE DEPT_ID = 20)
ORDER BY SALARY;

Result6 rows · 3 columns

EMP_ID	FIRST_NAME	SALARY
8	Grace	92000.00
5	Priya	96500.00
2	Marcus	96500.00
3	Lena	104000.00
1	Aisha	118000.00
6	Daniel	131000.00

The same six employees again. SOME and ANY are interchangeable. That is worth knowing mainly so that reading somebody else's SOME does not send you to the documentation.

Why a tie moves between the grids

Marcus and Priya are both on 96500.00, so ORDER BY SALARY does not decide which of them comes first, and across these three grids it does not always put them in the same order. That is Episode 1's point about unordered results turning up in practice. The three queries return the same set of rows, and nothing promised a sequence within a tie.

Now let's do the same thing with > ALL

ALL is the less ambiguous of the two. Greater than all of them can only mean greater than every single one, so the comparison has to hold against the whole list rather than against one member of it.

Query

SELECT EMP_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEES WHERE DEPT_ID = 20)
ORDER BY SALARY;

Result2 rows · 3 columns

EMP_ID	FIRST_NAME	SALARY
1	Aisha	118000.00
6	Daniel	131000.00

Two rows. Aisha and Daniel. And by the same reasoning we used before, if we have to beat every value then we have to beat the biggest one, so this should be a MAX query in disguise.

Query — the same question as > MAX

SELECT EMP_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPT_ID = 20)
ORDER BY SALARY;

Result2 rows · 3 columns

EMP_ID	FIRST_NAME	SALARY
1	Aisha	118000.00
6	Daniel	131000.00

The same two employees. So the whole family comes down to arithmetic we already know.

Written as	Means	Equivalent to
`x > ANY (list)`	greater than at least one value in the list	`x > MIN(list)`
`x > SOME (list)`	the same thing — `SOME` is a synonym	`x > MIN(list)`
`x > ALL (list)`	greater than every value in the list	`x > MAX(list)`

Flip the operator and the two swap over. < ANY is < MAX and < ALL is < MIN, for the same reason. The thing to carry is not the four cases but the sentence they come from. ANY needs one value to agree with it. ALL needs the lot.

One condition on that table

The two equivalences above hold for a list with no NULLs in it, which is the case here. A NULL in the list changes what > ALL does and does not change MAX, which ignores NULLs. We come back to that at the end of the post.

Can we use them with other operators? = ANY is IN

Nothing restricts these keywords to >. Any comparison operator works, and there is one combination worth recognising on sight, because you already use it daily.

= ANY (subquery) says my value equals at least one of theirs, and that is the definition of IN.

Query

SELECT DEPT_ID, DEPT_NAME
FROM DEPARTMENTS
WHERE DEPT_ID = ANY (SELECT DEPT_ID FROM EMPLOYEES)
ORDER BY DEPT_ID;

Result3 rows · 2 columns

DEPT_ID	DEPT_NAME
10	Engineering
20	Finance
30	Marketing

Three rows. Engineering, Finance and Marketing, the departments somebody works in. This is Episode 38's IN query with a longer spelling, and it gives us the identical result.

And the one it is worth translating back

The useful consequence is the other direction. <> ALL (subquery) is NOT IN, which reads as different from every one of them. It inherits NOT IN's NULL behaviour along with its meaning, because it is the same chain of comparisons joined by AND. If you meet it in somebody's code, read it as NOT IN and apply Episode 38's warning.

Now let's try it with an empty subquery

Here is the part of this post worth remembering after the rest has faded. What happens when the subquery returns no rows at all?

There is no department 99. Let's confirm that rather than assume it, because everything in this section rests on the list being empty.

Query — confirm the list is empty

SELECT COUNT(*) AS ROWS_IN_DEPT_99 FROM EMPLOYEES WHERE DEPT_ID = 99;

Result1 row · 1 column

ROWS_IN_DEPT_99
0

Nothing in department 99. So both of the next two subqueries return an empty list, and the only difference between the two queries is the keyword.

> ALL of an empty list

SELECT COUNT(*) AS N
FROM EMPLOYEES
WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEES WHERE DEPT_ID = 99);

Result1 row · 1 column

N
8

> ANY of an empty list

SELECT COUNT(*) AS N
FROM EMPLOYEES
WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEES WHERE DEPT_ID = 99);

Result1 row · 1 column

N
0

Every employee passes the first one. Nobody passes the second. Let's put them side by side, with the table's own count next to them.

Query — the whole table, and both empty-list counts

SELECT
  (SELECT COUNT(*) FROM EMPLOYEES)                                           AS ALL_EMPLOYEES,
  (SELECT COUNT(*) FROM EMPLOYEES
     WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEES WHERE DEPT_ID = 99))   AS GT_ALL_EMPTY,
  (SELECT COUNT(*) FROM EMPLOYEES
     WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEES WHERE DEPT_ID = 99))   AS GT_ANY_EMPTY;

Result1 row · 3 columns

ALL_EMPLOYEES	GT_ALL_EMPTY	GT_ANY_EMPTY
8	8	0

ALL_EMPLOYEES and GT_ALL_EMPTY are the same number. GT_ANY_EMPTY is nothing at all.

Both answers are defensible, said slowly

Query	Read as	Against an empty list	Result
`x > ALL (empty)`	greater than every one of them	there are none, so there is nothing to fail against	everybody passes
`x > ANY (empty)`	greater than at least one of them	there is not one, so there is nothing to beat	nobody passes

Neither one is a quirk. Both follow from the plain reading of the keyword once the list is empty. Both are also the opposite of what most people guess, which is exactly why they matter.

Where this becomes a bug you cannot see

The dangerous version of this is a subquery filtered by something that can legitimately match nothing: a date range, a region code, a status that nobody has yet. The query still runs, returns no error, and hands back either every row in the table or none. Both of those are plausible-looking results that no reviewer will question.

Can a NULL in the list break > ALL? Let's look carefully

One loose end. > ALL expands to a chain of comparisons joined by AND, and that is the same construction that made NOT IN collapse in Episode 38. So a NULL in the list ought to matter here too.

COMMISSION_PCT is the column to try it on, because most of it is empty.

Query

SELECT EMP_ID, FIRST_NAME, COMMISSION_PCT FROM EMPLOYEES ORDER BY EMP_ID;

Result8 rows · 3 columns

EMP_ID	FIRST_NAME	COMMISSION_PCT
1	Aisha	NULL
2	Marcus	NULL
3	Lena	0.05
4	Tomas	NULL
5	Priya	0.10
6	Daniel	NULL
7	Sofia	0.08
8	Grace	NULL

Three employees have a commission. The rest are NULL. Now let's run the same count twice. Once against the whole column, and once with the NULLs filtered out of the list.

> ALL, with the NULLs left in the list

SELECT COUNT(*) AS N
FROM EMPLOYEES
WHERE COMMISSION_PCT > ALL (SELECT COMMISSION_PCT FROM EMPLOYEES);

Result1 row · 1 column

N
0

> ALL, with the NULLs filtered out

SELECT COUNT(*) AS N
FROM EMPLOYEES
WHERE COMMISSION_PCT > ALL (SELECT COMMISSION_PCT FROM EMPLOYEES
                             WHERE COMMISSION_PCT IS NOT NULL);

Result1 row · 1 column

N
0

Both come back empty, and the two zeros do not have the same cause. The second one is ordinary arithmetic. The largest commission in the list is 0.10, and nobody in the table exceeds it, so there is nothing to return. The first one would be empty on this data whether the NULLs mattered or not.

What these two grids can and cannot show

So this pair does not demonstrate that a NULL in the list breaks > ALL. It cannot, because the control returns nothing either. The mechanism is real and Episode 38 showed it on data built for it: an AND chain containing an unknown comparison cannot come out TRUE. Showing these two grids is honest. Reading the first zero as proof of it would not be.

The practical advice does not depend on the proof. Filter NULLs out of any list you are about to use with ALL, the same way you would for NOT IN, and the question stops arising. If you want the behaviour demonstrated rather than reasoned about, you need a column where somebody actually exceeds the maximum. This one has nobody.

One last thing: when would we actually write these?

Honestly, not often. > ANY and > ALL are usually clearer as > MIN and > MAX, and = ANY is always clearer as IN. The reason to know them is that they turn up in code we did not write, and misreading one is easy.

Read > ANY as beats the weakest and > ALL as beats the best. The keyword that sounds like the high bar is the low one.
Translate = ANY to IN and <> ALL to NOT IN on sight, and then apply what you know about those.
When the subquery can return nothing, stop and work out which of the two answers you are about to get. ALL lets everybody through; ANY lets nobody.
Prefer EXISTS when the real question is is there a row like this, and the aggregate form when the real question is beat this threshold. Both say what they mean more plainly than these keywords do.

That is the whole family. Two readings of one English word, two aggregate functions they reduce to, and one empty-list case that is correct and surprising at the same time.

Key points

> ANY means greater than at least one value in the list, which makes it the same query as > MIN(list).
> ALL means greater than every value in the list, which makes it the same query as > MAX(list).
SOME is an alternative spelling of ANY, not a third behaviour. Swapping them returns the same rows.
A value is not greater than itself, so a row whose own value is in the list fails that comparison — and nothing stops the outer row and the list from overlapping.
= ANY (subquery) is IN, and <> ALL (subquery) is NOT IN — including its behaviour around NULL.
Against an empty subquery, > ALL is TRUE for every row: there is nothing to fail against.
Against the same empty subquery, > ANY is TRUE for no row at all: there is nothing to beat.
That empty-list pair is the bug to watch for. A subquery filtered by something that legitimately matches nothing returns either the whole table or none of it, with no error either way.
> ALL builds an AND chain, so filter NULLs out of the list by habit — the same precaution NOT IN needs.

Next in this series

In the next post we will cover correlated subqueries. Every subquery so far could be run on its own. The next one cannot, because it mentions a column from the outer query, so it is worked out once per outer row. That is what made the EXISTS queries in Episode 39 ask about each customer rather than about the table.

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, including both empty-list counts and the two zeros in the last section. The EMPLOYEES and DEPARTMENTS data is invented demo data created by the script above. No performance or benchmark claims are made anywhere in this series — the equivalences shown here are claims about results, not about how Snowflake executes them. Operator behaviour follows the Snowflake subquery-operator documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 40 - ANY, ALL and SOME in Snowflake

Let's create some sample data

Let's start with > ANY

Now let's check whether > ANY is just > MIN

Now let's swap `ANY` for `SOME`

Now let's do the same thing with > ALL

Can we use them with other operators? = ANY is IN

Now let's try it with an empty subquery

Both answers are defensible, said slowly

Can a NULL in the list break > ALL? Let's look carefully

One last thing: when would we actually write these?

Key points

No comments:

Post a Comment

Label

Snowflake SQL Tutorial 40 - ANY, ALL and SOME in Snowflake

Let's create some sample data

Let's start with > ANY

Now let's check whether > ANY is just > MIN

Now let's swap ANY for SOME

Now let's do the same thing with > ALL

Can we use them with other operators? = ANY is IN

Now let's try it with an empty subquery

Both answers are defensible, said slowly

Can a NULL in the list break > ALL? Let's look carefully

One last thing: when would we actually write these?

Key points

No comments:

Post a Comment

Now let's swap `ANY` for `SOME`