Snowflake SQL Tutorial 3 - WHERE Clause Explained - Comparison Operators + The NULL Trap

Snowflake SQL · Episode 3

In this post we are going to cover the WHERE clause in Snowflake.

WHERE is how we pick the rows we want. We give it a condition, Snowflake tests that condition against every row in the table, and we get back the rows it was true for.

We will start with one simple condition, then go through all the comparison operators one at a time. At the end we will split the table in two with a condition and its opposite, count the rows, and find out why they do not add up.

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

Episode 3 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 simple filter
3. Now let's filter on text
4. Now let's go through the comparison operators
5. Let's write not equal to, both ways
6. Now let's compare dates
7. Can the condition be a calculation?
8. Now let's split the table in two and count the rows
9. Let's get the missing row back with IS NULL

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 follow along. No sample database is needed.

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');

It is the same table the whole series uses, so you only ever set it up once.

Let's look at four of its columns with no filter on them at all. This is the baseline for every query below.

Query — no WHERE clause

SELECT FIRST_NAME, LAST_NAME, DEPT_ID, SALARY
FROM EMPLOYEES;

Result8 rows · 4 columns

FIRST_NAME	LAST_NAME	DEPT_ID	SALARY
Aisha	Khan	10	118000.00
Marcus	Reid	10	96500.00
Lena	Fischer	20	104000.00
Tomas	Novak	20	87250.00
Priya	Raman	30	96500.00
Daniel	O'Connor	10	131000.00
Sofia	Marino	30	71400.00
Grace	Lin	NULL	92000.00

That is everything the table holds. Have a look at the DEPT_ID on the last row. Grace has no department, so the column says NULL.

That is not decoration. It is the whole point of the last two sections of this post.

Let's start with one simple filter

We add the word WHERE after the table name, then a condition. Let's ask for the employees in department 10.

Query

SELECT FIRST_NAME, LAST_NAME, DEPT_ID, SALARY
FROM EMPLOYEES
WHERE DEPT_ID = 10;

Result3 rows · 4 columns

FIRST_NAME	LAST_NAME	DEPT_ID	SALARY
Aisha	Khan	10	118000.00
Marcus	Reid	10	96500.00
Daniel	O'Connor	10	131000.00

Eight rows became three: Aisha, Marcus and Daniel. Everybody whose DEPT_ID is not 10 has gone.

The documentation describes the clause in a single line. It “specifies a condition that acts as a filter”.

Here is the way to picture it, and the rest of this post is a consequence of it. Snowflake takes the condition and evaluates it against every row, one row at a time. If the answer is true, the row is in our result. If it is anything other than true, the row is not.

Hold on to those last four words. “Anything other than true” is doing more work in that sentence than it looks like it is.

Now let's filter on text

= works on text exactly as it does on numbers. A string goes in single quotes. Let's look for Khan.

Query

SELECT FIRST_NAME, LAST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'Khan';

Result1 row · 3 columns

FIRST_NAME	LAST_NAME	DEPT_ID
Aisha	Khan	10

One row, which is what we expected.

Now let's type the same name in capitals

We will change nothing except the case of the value.

Query

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME = 'KHAN';

Result0 rows · 2 columns

FIRST_NAME	LAST_NAME

Zero rows. String comparison in Snowflake is case sensitive, so 'KHAN' is simply not the same string as 'Khan'.

An empty result is not the same as no data

This is a trap precisely because nothing goes wrong. There is no error, no warning and no hint. We get an empty result, which looks exactly like no matching data, and then we go looking for the problem in the pipeline, the load, or the source system. It was the capital letters all along.

Quotes are not interchangeable

Single quotes make a string literal. Double quotes mean something else entirely in Snowflake. They make a quoted identifier, so "Khan" asks for a column named Khan and not a value. That is Episode 12.

Now let's go through the comparison operators

These are the operators WHERE gives us. The list itself holds no surprises.

Operator	Means	Note
=	Equal to	Case sensitive on strings
!=	Not equal to	Identical to <>
<>	Not equal to	Identical to !=
>	Greater than	Excludes the boundary value
<	Less than	Excludes the boundary value
>=	Greater than or equal to	Includes the boundary value
<=	Less than or equal to	Includes the boundary value

Two of them are worth running rather than reading, because they look almost the same as the operator next to them. Let's take them in order.

Greater than

Query

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 100000;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Lena	104000.00
Daniel	131000.00

Three people earn more than 100,000.

Now let's use greater than or equal to

In a reference manual this reads like a technicality. On real data it is not. Let's filter at a salary two of our employees earn exactly.

Query

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY >= 96500;

Result5 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00
Lena	104000.00
Priya	96500.00
Daniel	131000.00

Five rows, not three. Two employees earn exactly 96,500, and >= includes them while > would have thrown both away. If you mean at least, do not type >.

And less than

Query

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < 90000;

Result2 rows · 2 columns

FIRST_NAME	SALARY
Tomas	87250.00
Sofia	71400.00

Two rows. <= exists as well, and it relates to < exactly as >= relates to >.

Let's write not equal to, both ways

Snowflake accepts two spellings of not equal to. Let's start with !=.

Query

SELECT FIRST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID != 10;

Result4 rows · 2 columns

FIRST_NAME	DEPT_ID
Lena	20
Tomas	20
Priya	30
Sofia	30

Now let's write the same filter with the angle brackets.

Query

SELECT FIRST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID <> 10;

Result4 rows · 2 columns

FIRST_NAME	DEPT_ID
Lena	20
Tomas	20
Priya	30
Sofia	30

The same rows. != and <> are the same operator, not two operators with similar behaviour, so there is nothing to choose between them on correctness.

Use whichever spelling your team already uses, and use it consistently. Mixing both in one codebase makes a search for every inequality in your SQL harder than it needs to be.

Remember this one

Hold on to the row count from these two queries. It is the first half of the arithmetic that does not work, further down this page.

Now let's compare dates

These are not numeric operators. They work on dates as readily as on numbers and text. A date literal is a string in single quotes, in YYYY-MM-DD form.

Query

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE > '2021-01-01';

Result4 rows · 2 columns

FIRST_NAME	HIRE_DATE
Marcus	2021-07-01
Tomas	2022-09-05
Sofia	2023-02-13
Grace	2021-04-19

The four employees hired after 2021-01-01. On a date, > means later than and < means earlier than. The operator did not change, only the type it is comparing.

Can the condition be a calculation?

Every query so far has compared a plain column against a value, which is what most WHERE clauses look like. It is not what the clause requires.

The documentation calls the condition “A Boolean expression. The expression can include logical operators, such as AND, OR, and NOT.” An expression, so the left-hand side can be worked out for us.

Let's take ten percent of the salary and test that against 10,000.

Query

SELECT FIRST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE SALARY * 0.10 > 10000;

Result3 rows · 3 columns

FIRST_NAME	SALARY	COMMISSION_PCT
Aisha	118000.00	NULL
Lena	104000.00	0.05
Daniel	131000.00	NULL

Three rows. COMMISSION_PCT is in the output because we selected it, and it played no part in the filter at all. Two of these three employees have no commission recorded and the third has 0.05, and none of that was consulted. The expression was built from SALARY.

Filtering and selecting are separate jobs

SELECT and WHERE are independent. A column can be filtered on without being selected, and selected without being filtered on. The expression in a WHERE clause does not have to appear in the result at all.

Now let's split the table in two and count the rows

This is the part worth slowing down for. Let's take the filter from the beginning of the post, this time on two columns.

Query — part 1

SELECT FIRST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID = 10;

Result3 rows · 2 columns

FIRST_NAME	DEPT_ID
Aisha	10
Marcus	10
Daniel	10

Three rows match DEPT_ID = 10. The table has eight rows, so DEPT_ID <> 10 should return the other five. Let's run it.

Query — part 2

SELECT FIRST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID <> 10;

Result4 rows · 2 columns

FIRST_NAME	DEPT_ID
Lena	20
Tomas	20
Priya	30
Sofia	30

Four. Not five. Three plus four is seven, and the table has eight rows. So one employee is in neither result, and rereading the two conditions does not explain it, because there is nothing wrong with either of them.

It is Grace. Her DEPT_ID is NULL, and NULL is not a value. It is the absence of one.

So the question is Grace's DEPT_ID equal to 10? is neither true nor false. It is unknown. And the question is it not equal to 10? is unknown as well. Unknown is not true, and WHERE only keeps the rows its condition is true for.

Why the arithmetic fails

The documentation states the consequence directly: “In a WHERE clause, if an expression evaluates to NULL, the row for that expression is removed from the result set.”
Removed. Not kept, not treated as false, not reported. Removed. So a condition and its own negation can both exclude the same row, and the two row counts will not add up to the table.

This matters well beyond a counting puzzle on eight rows. Any time we split a table into a group and “everybody else” with a condition and its negation, every row whose filtered column is NULL falls silently out of both halves. That is reconciling two reports, bucketing customers, validating a migration.

Let's get the missing row back with IS NULL

To get Grace back we need an operator that asks a question NULL can answer.

We cannot write DEPT_ID = NULL. From the documentation, “In most contexts, the Boolean expression NULL = NULL returns NULL, not TRUE”, and it recommends IS [NOT] NULL for comparing NULL values instead. Let's use that.

Query

SELECT FIRST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID IS NULL;

Result1 row · 2 columns

FIRST_NAME	DEPT_ID
Grace	NULL

There she is. IS NULL does not compare the column to a value. It asks whether the column has a value at all, which is a question with a true-or-false answer.

NULL gets a whole post of its own, Episode 6, because the same three-valued logic turns up again in NOT IN, in joins and in aggregates. It is worth understanding once, properly.

Key points

• WHERE “specifies a condition that acts as a filter”: the condition is evaluated against every row, and the row is returned only when the answer is true.
• String comparison is case sensitive. 'KHAN' and 'Khan' are different strings, and the mismatch returns an empty result rather than an error.
• > excludes the boundary value and >= includes it. That is a real difference whenever somebody sits exactly on the number you filtered by.
• != and <> are the same operator, with nothing to choose between them but consistency.
• Comparison operators work on dates and text, not only numbers.
• The condition is “A Boolean expression”, so the left-hand side can be calculated. And a column can be filtered on without being selected.
• A condition and its negation do not have to cover the whole table. When an expression evaluates to NULL, “the row for that expression is removed from the result set” in both directions.
• Use IS NULL and IS NOT NULL to ask about missing values. = NULL returns NULL, not TRUE, so it never matches anything.

Next in this series

In the next post we will cover BETWEEN, IN, LIKE, ILIKE and RLIKE. These are the operators that make a WHERE clause much shorter to write: ranges, lists and pattern matching.

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 WHERE documentation.

Snowflake SQL Tutorial 2 - FROM Clause Explained - Table Aliases, Qualified Names & SELECT With No Table

Snowflake SQL · Episode 2

In this post we are going to cover the FROM clause in Snowflake.

FROM is the part of the query that says where the rows come from. SELECT chooses the columns, and FROM names the table those columns live in. That is the whole division of labour between the two.

We will start with a plain table name. Then we will give the table a short name of our own, then write its full three-part name, and at the end we will run a SELECT with no table in it at all.

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

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

On this page

1. Let's create some sample data
2. Let's see which clause does which job
3. Now let's see what else can go in a FROM clause
4. Let's give the table a short name
5. Now let's try the full table name after aliasing it
6. Let's write the full DATABASE.SCHEMA.TABLE name
7. Can we run a SELECT with no table at all?
8. One last thing: the documentation never says FROM is optional

Let's create some sample data

Everything on this page queries one small table called EMPLOYEES. If you already ran the setup script from Episode 1 you have it. If not, paste the script below into a worksheet and run it once.

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');

Every post in this series uses the same table, so you only have to do this one time.

Now let's look at what we just created.

Query

SELECT * FROM EMPLOYEES;

Result8 rows · 9 columns · scroll →

EMP_ID	FIRST_NAME	LAST_NAME	EMAIL	DEPT_ID	MANAGER_ID	SALARY	COMMISSION_PCT	HIRE_DATE
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

Eight rows and nine columns. Keep that row count in mind. For the rest of this page, the number of rows coming back is a fact about the FROM clause, and the columns are a fact about SELECT.

Let's see which clause does which job

Let's ask for two columns instead of all nine, and leave the FROM clause exactly as it was.

Query

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES;

Result8 rows · 2 columns

FIRST_NAME	LAST_NAME
Aisha	Khan
Marcus	Reid
Lena	Fischer
Tomas	Novak
Priya	Raman
Daniel	O'Connor
Sofia	Marino
Grace	Lin

Two columns instead of nine, because the SELECT list changed. Still eight rows, because the FROM clause did not. EMPLOYEES holds eight rows, so a query with no filtering on it returns eight rows.

So changing the SELECT list can never change how many rows you get. Narrowing rows is a different job, done by a different clause, and that clause is the subject of the next post.

The documentation is precise about which half FROM owns: “Specifies the tables, views, or table functions to use in a SELECT statement.”

Now let's see what else can go in a FROM clause

Read that documentation sentence again and notice what it does not say. It does not say “tables”. A table is the common case, not the only one.

What you can put in FROM	Example
A table	FROM EMPLOYEES
A view	FROM V_ACTIVE_EMPLOYEES
A table function	FROM TABLE(GENERATOR(ROWCOUNT => 5))
A subquery	FROM (SELECT * FROM EMPLOYEES) S
A VALUES list	FROM (VALUES (1,'a'), (2,'b')) AS T(ID, TAG)
A file on a stage	FROM @MY_STAGE/data.csv
A join of any of the above	FROM EMPLOYEES E JOIN DEPARTMENTS D ON …

Each row in that table is a topic in its own right, and most of them get a post later in the series. Joins get a whole level to themselves.

The shape of the idea is what matters today. FROM takes something that produces rows, and a table is only the most obvious example of that.

Let's give the table a short name

We can give a table a short name in the FROM clause and then use that short name everywhere else in the query. Let's call EMPLOYEES just E.

Query

SELECT E.FIRST_NAME, E.SALARY
FROM EMPLOYEES AS E;

Result8 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00
Lena	104000.00
Tomas	87250.00
Priya	96500.00
Daniel	131000.00
Sofia	71400.00
Grace	92000.00

E is an alias. It lets us write E.FIRST_NAME instead of EMPLOYEES.FIRST_NAME.

On a one-table query that is a mild convenience. On a query joining three or four tables it is the difference between something you can read and something you cannot.

Now let's drop the AS

The AS is optional here. From the documentation: “The AS keyword is optional when specifying an alias.” Let's run the same query without it.

Query — the AS dropped

SELECT E.FIRST_NAME, E.SALARY
FROM EMPLOYEES E;

Result8 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00
Lena	104000.00
Tomas	87250.00
Priya	96500.00
Daniel	131000.00
Sofia	71400.00
Grace	92000.00

FROM EMPLOYEES E and FROM EMPLOYEES AS E are the same query. Same columns, same values, same eight rows. The AS is punctuation we are allowed to leave out.

Worth a habit

Optional is not the same as pointless. Spelling out AS makes an alias obvious at a glance. And in the SELECT list, a dropped AS together with a missing comma silently turns one column into an alias for another. Leaving it out of FROM is harmless, but the habit of writing it is worth keeping anyway.

Now let's try the full table name after aliasing it

We have just aliased EMPLOYEES to E. Let's see what happens if we go on calling it EMPLOYEES in the same query.

This will not work

-- Once the table has an alias, the alias IS the name.
-- EMPLOYEES is no longer something this query can refer to.
SELECT EMPLOYEES.FIRST_NAME
FROM EMPLOYEES AS E;

Watch out

The alias replaces the table name for the rest of the query. It does not add a second way to refer to it. So E.FIRST_NAME resolves and EMPLOYEES.FIRST_NAME does not. There is no result grid for the query above because it was never run for this post. Run it in your own worksheet and read the error Snowflake gives you, because that is the error you will meet again later on a real query, with less patience.

This one catches everybody exactly once. Once a table is aliased, the alias is the name.

Let's write the full DATABASE.SCHEMA.TABLE name

Every query so far has written EMPLOYEES as one bare word, and it has worked. That is not because tables have one-word names. It is because the session already knows where to look.

Let's ask the session what it thinks.

Query

SELECT CURRENT_DATABASE() AS CURRENT_DB,
       CURRENT_SCHEMA()   AS CURRENT_SCH,
       CURRENT_WAREHOUSE() AS CURRENT_WH;

Result1 row · 3 columns

CURRENT_DB	CURRENT_SCH	CURRENT_WH
DEMO_DB	PUBLIC	COMPUTE_WH

A current database, a current schema, and the warehouse doing the work. Given that context, EMPLOYEES is shorthand for a name with three parts.

Part of the name	Value in this session	Reported by
Database	DEMO_DB	CURRENT_DATABASE()
Schema	PUBLIC	CURRENT_SCHEMA()
Table	EMPLOYEES	the only part you wrote

Now let's write all three parts, separated by dots.

Query — fully qualified

SELECT * FROM DEMO_DB.PUBLIC.EMPLOYEES;

Result8 rows · 9 columns · scroll →

EMP_ID	FIRST_NAME	LAST_NAME	EMAIL	DEPT_ID	MANAGER_ID	SALARY	COMMISSION_PCT	HIRE_DATE
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

The same result as the bare EMPLOYEES query at the top of this page, because it is the same work. The two names point at the same table.

The documentation puts the rule the other way round, as permission to leave the parts off: “A namespace is not required if the context can be derived from the current database and schema for the session.”

So when should we write it out anyway?

• The table lives in another database or schema than the one our session is pointed at.
• The query must mean the same thing whoever runs it, whatever their session happens to be set to.
• It is a view definition, a saved query, or a scheduled task. Anything that will run later, without us watching, in a session we did not set up.

In a worksheet the bare name is fine and faster to type. Anything we are saving, sharing or scheduling gets the full three-part name.

Can we run a SELECT with no table at all?

Yes, and this is the one that surprises people. The query below comes straight out of Snowflake's own SELECT documentation, and there is no FROM clause anywhere in it.

Query

SELECT PI() * 2.0 * 2.0 AS AREA_OF_CIRCLE;

Result1 row · 1 column

AREA_OF_CIRCLE
12.566370614359172

One row, one column, and no table was involved in producing it.

The documentation explains its own example like this: “This example shows that the output columns do not need to be taken directly from the tables in the FROM clause; the output columns can be general expressions.”

That is the lesson from Episode 1 taken to its limit. SELECT evaluates expressions, and an expression does not need a row to sit in.

Now let's use that as a scratchpad

The practical use is checking what a function does before we point it at anything that matters.

Query

SELECT UPPER('techbrothers') AS SHOUT,
       LENGTH('snowflake')   AS LEN,
       CURRENT_DATE()        AS TODAY;

Result1 row · 3 columns

SHOUT	LEN	TODAY
TECHBROTHERS	9	2026-09-23

Three expressions, three columns, one row, and no data touched. This is the fastest way in Snowflake to answer “what does that function actually return?”, and you will use it constantly once you know it is there.

One last thing: the documentation never says FROM is optional

There is an obvious conclusion to draw from that last section, and this series is not going to draw it for you.

Looking for a documented statement that the FROM clause is optional turns up nothing. The SELECT documentation shows the query above and explains why output columns need not come from a table. It states no rule about FROM being optional, and the Snowflake FROM clause documentation does not address the question at all.

Shown, not claimed

So here is what can be shown and nothing more: this is the example the documentation gives, and it runs. A worked example is not a rule. Where this series quotes a rule, there is a documented sentence behind it. Where there is only an example, you get the example and a note like this one.

Key points

• SELECT says which columns you get, and FROM says where the rows come from. Changing the SELECT list cannot change the row count.
• FROM accepts more than tables. Views, table functions, subqueries, VALUES lists, staged files and joins all go in the same place.
• A table alias gives the table a short name: FROM EMPLOYEES AS E lets you write E.FIRST_NAME.
• The AS is optional. FROM EMPLOYEES E is the same query as FROM EMPLOYEES AS E.
• Once a table is aliased, the alias is the name. EMPLOYEES.FIRST_NAME no longer resolves.
• A bare table name works because the session supplies the missing parts. CURRENT_DATABASE() and CURRENT_SCHEMA() show you which ones.
• Write DATABASE.SCHEMA.TABLE whenever the query will outlive your session: views, saved queries, scheduled tasks.
• A SELECT with no FROM runs and returns one row of expressions, which makes it a quick way to test a function.
• The documentation shows that no-FROM query but never states that FROM is optional. Example, not rule.

Next in this series

In the next post we will cover the WHERE clause. We start filtering rows, and we meet the comparison operators that go in the clause that does it.

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 FROM clause documentation and the SELECT documentation.

Snowflake SQL Tutorial 1 - SELECT Statement Explained - EXCLUDE, RENAME, REPLACE & ILIKE

Snowflake SQL · Episode 1

In this post we are going to cover the SELECT statement in Snowflake.

SELECT is how you read data. You tell Snowflake which columns you want and which table they come from, and Snowflake gives you back a result. It is the first statement you learn and the one you will run more times than all the others put together.

We will start with the simplest form and build it up step by step. By the end we will also have covered four things you can do with SELECT * in Snowflake that a lot of SQL developers have never seen: EXCLUDE, RENAME, REPLACE and ILIKE.

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

Episode 1 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 the simplest SELECT
3. Now let's change the order of the columns
4. Now let's do a calculation inside SELECT
5. Let's give our calculated columns a name with AS
6. Now let's select everything with *
7. Let's drop a few columns with EXCLUDE
8. Let's rename a column with RENAME
9. Now let's change the values with REPLACE
10. Let's pick columns by name with ILIKE
11. Can we use them together?
12. One last thing: SELECT does not promise an order

Let's create some sample data

Before we can select anything we need a table to select from. The script below creates a small EMPLOYEES table and puts eight rows into it.

Copy this into a Snowflake worksheet and run it once. Every post in this series uses the same table, so you only have to do this one time.

Sample data

-- Sample data for this post.
-- Run this once. Every post in this series uses the same EMPLOYEES table.
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');

Now let's look at what we just created.

Query

SELECT * FROM EMPLOYEES;

Result8 rows · 9 columns · scroll →

EMP_ID	FIRST_NAME	LAST_NAME	EMAIL	DEPT_ID	MANAGER_ID	SALARY	COMMISSION_PCT	HIRE_DATE
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

Eight rows and nine columns. We have an employee ID, a first and last name, an email address, a department ID, a manager ID, a salary, a commission percentage and a hire date.

A few cells say NULL. That is on purpose, and we will come back to them in a later post.

Let's start with the simplest SELECT

The simplest SELECT is the word itself, then the columns we want separated by commas, then FROM and the name of the table.

Let's ask for just three columns.

Query

SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES;

Result8 rows · 3 columns

FIRST_NAME	LAST_NAME	SALARY
Aisha	Khan	118000.00
Marcus	Reid	96500.00
Lena	Fischer	104000.00
Tomas	Novak	87250.00
Priya	Raman	96500.00
Daniel	O'Connor	131000.00
Sofia	Marino	71400.00
Grace	Lin	92000.00

We got back three columns instead of nine. The other six were not hidden from us. They were simply never part of what we asked for.

This is worth getting clear on day one. In a SELECT we describe the result we want, and that is what comes back. Nothing more.

Now let's change the order of the columns

The order we list the columns in is the order they come back in. Let's move SALARY to the front and run the same query again.

Query

SELECT SALARY, FIRST_NAME, LAST_NAME
FROM EMPLOYEES;

Result8 rows · 3 columns

SALARY	FIRST_NAME	LAST_NAME
118000.00	Aisha	Khan
96500.00	Marcus	Reid
104000.00	Lena	Fischer
87250.00	Tomas	Novak
96500.00	Priya	Raman
131000.00	Daniel	O'Connor
71400.00	Sofia	Marino
92000.00	Grace	Lin

SALARY is the first column now. The table has not changed at all. The only thing that changed is how we described the result.

Now let's do a calculation inside SELECT

SELECT does not only fetch columns. It can work things out for us as well.

Let's ask for the monthly pay, which is the salary divided by twelve, and the last name in capital letters.

Query

SELECT FIRST_NAME,
       SALARY / 12,
       UPPER(LAST_NAME)
FROM EMPLOYEES;

Result8 rows · 3 columns

FIRST_NAME	SALARY / 12	UPPER(LAST_NAME)
Aisha	9833.33333333	KHAN
Marcus	8041.66666667	REID
Lena	8666.66666667	FISCHER
Tomas	7270.83333333	NOVAK
Priya	8041.66666667	RAMAN
Daniel	10916.66666667	O'CONNOR
Sofia	5950.00000000	MARINO
Grace	7666.66666667	LIN

Neither of those is a column in our table. Snowflake calculated both of them for this result and then forgot them again.

Have a look at the column headings though. They are the calculation itself, written out. That works, but nobody wants to see UPPER(LAST_NAME) at the top of a report.

Let's give our calculated columns a name with AS

We can give any column a name of our own using the word AS. Let's call these two MONTHLY_PAY and SURNAME.

Query

SELECT FIRST_NAME,
       SALARY / 12      AS MONTHLY_PAY,
       UPPER(LAST_NAME) AS SURNAME
FROM EMPLOYEES;

Result8 rows · 3 columns

FIRST_NAME	MONTHLY_PAY	SURNAME
Aisha	9833.33333333	KHAN
Marcus	8041.66666667	REID
Lena	8666.66666667	FISCHER
Tomas	7270.83333333	NOVAK
Priya	8041.66666667	RAMAN
Daniel	10916.66666667	O'CONNOR
Sofia	5950.00000000	MARINO
Grace	7666.66666667	LIN

Same values, readable headings. That name is called an alias. It lives only for this one result and it changes nothing in the table.

A habit worth having

AS is optional in Snowflake. You can write SALARY / 12 MONTHLY_PAY and it works. Write the AS anyway. If you ever forget a comma between two columns, Snowflake reads the second one as an alias for the first, the query still runs, and you get back the wrong shape with no error at all.

Now let's select everything with *

If we want every column, we can write * instead of naming them.

Query

SELECT * FROM EMPLOYEES;

Result8 rows · 9 columns · scroll →

EMP_ID	FIRST_NAME	LAST_NAME	EMAIL	DEPT_ID	MANAGER_ID	SALARY	COMMISSION_PCT	HIRE_DATE
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

All nine columns and all eight rows, and we did not have to know a single column name to write it. When you are looking at a table for the first time, this is exactly the right thing to use.

One thing to watch

It is worth knowing what we are really asking for here. * does not mean “these nine columns”. It means whatever columns exist when the query runs. If somebody adds two columns tomorrow, this same query starts returning eleven. That is fine in a worksheet. It is a real problem inside a view or an INSERT ... SELECT.

So * is convenient, but a little blunt. This is where Snowflake gives us something most other databases do not. There are four ways to keep * and stay in control of it, and we will go through them one at a time.

Let's drop a few columns with EXCLUDE

The first one is EXCLUDE. We write *, then EXCLUDE, then the columns we do not want.

Let's take everything except the salary and the email address.

Query

SELECT * EXCLUDE (SALARY, EMAIL)
FROM EMPLOYEES;

Result8 rows · 7 columns · scroll →

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

Nine columns became seven. SALARY and EMAIL are gone, and we never had to type out the seven we wanted to keep.

The documentation puts it in one line: “Specifies the columns that should be excluded from the results.”

On a small table like ours that is a nice convenience. On a table with forty columns, where you want everything except two sensitive ones, it saves you naming thirty-eight columns by hand and then keeping that list correct for ever.

Let's rename a column with RENAME

The second one is RENAME. It changes what a column is called in the result and leaves everything else alone.

Let's give EMP_ID a friendlier name.

Query

SELECT * RENAME (EMP_ID AS EMPLOYEE_NUMBER)
FROM EMPLOYEES;

Result8 rows · 9 columns · scroll →

EMPLOYEE_NUMBER	FIRST_NAME	LAST_NAME	EMAIL	DEPT_ID	MANAGER_ID	SALARY	COMMISSION_PCT	HIRE_DATE
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

We still have all nine columns and all the same values. Only the first heading changed, from EMP_ID to EMPLOYEE_NUMBER.

The documentation says: “Specifies the column aliases that should be used in the results.” It is AS, for a column we did not have to name individually.

Now let's change the values with REPLACE

The third one is REPLACE. Go slowly with this one, because the name sounds like RENAME and it does something completely different.

Let's round the salary to the nearest thousand and keep calling the column SALARY.

Query

SELECT * REPLACE (ROUND(SALARY, -3) AS SALARY)
FROM EMPLOYEES;

Result8 rows · 9 columns · scroll →

EMP_ID	FIRST_NAME	LAST_NAME	EMAIL	DEPT_ID	MANAGER_ID	SALARY	COMMISSION_PCT	HIRE_DATE
1	Aisha	Khan	aisha.khan@example.com	10	6	118000	NULL	2019-03-11
2	Marcus	Reid	marcus.reid@example.com	10	6	97000	NULL	2021-07-01
3	Lena	Fischer	lena.fischer@example.com	20	6	104000	0.05	2018-01-22
4	Tomas	Novak	tomas.novak@example.com	20	3	87000	NULL	2022-09-05
5	Priya	Raman	priya.raman@example.com	30	6	97000	0.10	2020-11-17
6	Daniel	O'Connor	daniel.oconnor@example.com	10	NULL	131000	NULL	2016-05-30
7	Sofia	Marino	sofia.marino@example.com	30	5	71000	0.08	2023-02-13
8	Grace	Lin	grace.lin@example.com	NULL	6	92000	NULL	2021-04-19

Still nine columns, and the column is still called SALARY. But look at the values. 118000.00 is now 118000, and 96500.00 is now 97000. They have been rounded.

The documentation says: “Replaces the value of column name with the value of the evaluated expression.”

The difference in one line

RENAME changes the name and leaves the values alone.
REPLACE leaves the name alone and changes the values.
Two similar words doing opposite jobs. This is the pair people mix up.

Let's pick columns by name with ILIKE

The fourth one is ILIKE, and we need to go slowly here too, because you have probably met ILIKE before in a WHERE clause.

In a WHERE clause it matches values, ignoring upper and lower case. Let's see that first.

ILIKE in a WHERE clause

SELECT * FROM EMPLOYEES
WHERE LAST_NAME ILIKE '%khan%';

Result1 row · 9 columns · scroll →

EMP_ID	FIRST_NAME	LAST_NAME	EMAIL	DEPT_ID	MANAGER_ID	SALARY	COMMISSION_PCT	HIRE_DATE
1	Aisha	Khan	aisha.khan@example.com	10	6	118000.00	NULL	2019-03-11

One row came back, because one employee's last name matches the pattern. That is the ILIKE you already know.

Now let's put it on * instead

When we attach ILIKE to *, it does not look at the values at all. The documentation says: “Specifies that only the columns that match pattern should be included in the results.”

Let's ask for every column whose name contains the word NAME.

ILIKE on *

SELECT * ILIKE '%NAME%'
FROM EMPLOYEES;

Result8 rows · 2 columns

FIRST_NAME	LAST_NAME
Aisha	Khan
Marcus	Reid
Lena	Fischer
Tomas	Novak
Priya	Raman
Daniel	O'Connor
Sofia	Marino
Grace	Lin

Two columns came back, FIRST_NAME and LAST_NAME. All eight rows are still there. Not a single row was filtered out.

So the same word does two different jobs, and which job it does depends on where we put it. In a WHERE clause it filters rows. On * it filters columns.

Can we use them together?

Yes, but not in every combination and not in any order we like. Snowflake documents exactly which combinations are allowed, and it is worth checking this list before you spend twenty minutes thinking something is broken.

Allowed combination	What it does
EXCLUDE + REPLACE	Drop some columns, change the values of others
EXCLUDE + RENAME	Drop some columns, rename others
EXCLUDE + REPLACE + RENAME	All three, written in that order
ILIKE + REPLACE	Pick columns by name, change their values
ILIKE + RENAME	Pick columns by name, rename them
ILIKE + REPLACE + RENAME	All three, written in that order
REPLACE + RENAME	Change the values, then rename

Two things come out of that list. ILIKE and EXCLUDE never appear together, because they are both ways of choosing which columns you get, so you pick one or the other. And RENAME, whenever it appears, is always written last.

Let's try one that is allowed. We will drop the email column and rename the ID column.

Query

SELECT * EXCLUDE (EMAIL) RENAME (EMP_ID AS ID)
FROM EMPLOYEES;

Result8 rows · 8 columns · scroll →

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

Eight columns instead of nine, with EMAIL gone and EMP_ID now called ID. If a combination of yours gives you an error, check it against the table above before you check anything else.

One last thing: SELECT does not promise an order

There is one more line in the documentation that catches people out, and it is worth reading twice: “Without an ORDER BY clause, the results returned by SELECT are an unordered set.”

Let's run a simple query and look at the order of the rows.

Query

SELECT FIRST_NAME, SALARY FROM EMPLOYEES;

Result8 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00
Lena	104000.00
Tomas	87250.00
Priya	96500.00
Daniel	131000.00
Sofia	71400.00
Grace	92000.00

On this run the rows came back in the order we inserted them. That is a coincidence, not a promise. Snowflake is free to return those eight rows in any order it likes, and on a bigger table it very often will.

If the order matters to you, you have to ask for it with ORDER BY. We will cover that properly in its own post.

Key points

• SELECT describes the result you want, and the columns come back in the order you list them.
• SELECT can calculate as well as fetch. Give the result a name with AS.
• * means whatever columns exist when the query runs, not the columns that exist today.
• EXCLUDE gives you everything except the columns you name.
• RENAME changes the name. REPLACE changes the values. Do not mix those two up.
• ILIKE on * picks columns by name. ILIKE in a WHERE clause filters rows by value.
• ILIKE and EXCLUDE cannot be used together, and RENAME is always written last.
• No ORDER BY means no guaranteed order.

Next in this series

In the next post we will look at the FROM clause properly: table names, aliases, fully qualified DATABASE.SCHEMA.TABLE names, and a SELECT statement with no table at all.

Every query and every result on this page was run on a Snowflake account (version 10.34.101) using the sample script above. The EMPLOYEES data is made up for teaching. Nothing here is timed and no performance claims are made anywhere in this series. The clause descriptions are quoted from the Snowflake SELECT documentation.