Snowflake SQL Tutorial 10 - You CAN Use a Column Alias in WHERE - in Snowflake - Aliases & AS

Snowflake SQL · Episode 10

In this post we are going to cover column aliases and the word AS in Snowflake.

An alias is a name we give a column in the result. We write the column or the calculation, then AS, then the name we want at the top of it. Nothing in the table changes. Only the heading we get back does.

We will start with some sample data, then see what Snowflake calls a calculated column when we name nothing, then name it ourselves. After that we will put spaces and capitals in an alias, use an alias again later in the same query, and build one alias out of another.

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

Episode 10 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 a query that names nothing
3. Now let's leave the AS out
4. Now let's put spaces and capitals in an alias
5. Can we use an alias again later in the query?
6. Now let's build an alias out of another alias
7. What if our alias has the same name as a real column?
8. One last thing: tables get aliases too

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

Every column in that table already has a name, because the CREATE TABLE gave it one. Those names are perfectly good. The trouble starts the moment we calculate something, because then there is no name to inherit.

FIRST_NAME, LAST_NAME and SALARY are the three columns we are going to rename, divide and generally interfere with.

Let's start with a query that names nothing

Here is a query with no aliases anywhere in it. Have a look at the headings of the result rather than the data.

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

The second column is called SALARY / 12 and the third is called UPPER(LAST_NAME). Snowflake used the text of the expression as the column name, because nothing better was supplied. That is honest, and it is fine in a worksheet nobody else is going to read.

It is not fine anywhere else. A report heading that reads UPPER(LAST_NAME) looks broken to the person reading it, and any code downstream that expects a sensibly named column will not find one.

So let's give them names with AS

Query

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

Result8 rows · 3 columns

GIVEN_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 rows, same values, readable headings: GIVEN_NAME, MONTHLY_PAY and SURNAME. An alias renames the column in the output and changes nothing about the table. FIRST_NAME is still called FIRST_NAME the moment this query finishes.

Now let's leave the AS out

The word AS is optional. We can put the name straight after the expression and Snowflake will not mind. Let's try that on the first two columns.

Query

SELECT FIRST_NAME GIVEN_NAME, SALARY / 12 MONTHLY_PAY FROM EMPLOYEES;

Result8 rows · 2 columns

GIVEN_NAME	MONTHLY_PAY
Aisha	9833.33333333
Marcus	8041.66666667
Lena	8666.66666667
Tomas	7270.83333333
Priya	8041.66666667
Daniel	10916.66666667
Sofia	5950.00000000
Grace	7666.66666667

GIVEN_NAME and MONTHLY_PAY, aliased the same way as before, with no keyword in front of them. So why write AS at all? Because of the next query.

Now let's leave out a comma as well

Read this one carefully before you look at the result. We wanted three columns: a first name, a last name and a salary. The comma between the last two is missing.

Query — the comma that is not there

SELECT FIRST_NAME, LAST_NAME SALARY FROM EMPLOYEES ORDER BY FIRST_NAME;

Result8 rows · 2 columns

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

This is not an error. It runs, and it gives us two columns instead of three.

Without that comma, SALARY is not a column any more. It is the alias for LAST_NAME. The heading still says SALARY, which is the cruel part, and the values underneath it are surnames: Khan, O'Connor, Lin.

Now let's put the comma back.

Query

SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES ORDER BY FIRST_NAME;

Result8 rows · 3 columns

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

Three columns, and the salaries are numbers again.

Why the optional keyword earns its place

Nothing in the broken version tells you anything is wrong. If you are eyeballing a report, or feeding the result into something that reads the SALARY column by name, it gets surnames and carries on.
Had AS been written throughout, that typo would have been a syntax error instead of a wrong answer. That is the entire argument for writing a keyword you do not need.

Now let's put spaces and capitals in an alias

Sometimes the heading we want is not a legal identifier. It has a space in it, or proper capital letters, because a person is going to read it. Double quotes let us ask for exactly that.

Query

SELECT FIRST_NAME AS "Given Name", SALARY AS "Annual Salary (USD)" FROM EMPLOYEES;

Result8 rows · 2 columns

Given Name	Annual Salary (USD)
Aisha	118000.00
Marcus	96500.00
Lena	104000.00
Tomas	87250.00
Priya	96500.00
Daniel	131000.00
Sofia	71400.00
Grace	92000.00

Exactly the headings we asked for, capitals, spaces and brackets included.

Now let's try to use that alias somewhere else

We defined the alias as "Given Name". Here we refer to it as GIVEN_NAME, unquoted and in capitals, which is the spelling most people would type without thinking.

Query — this one fails

SELECT FIRST_NAME AS "Given Name" FROM EMPLOYEES ORDER BY GIVEN_NAME;

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 58
invalid identifier 'GIVEN_NAME'

It failed, and not because the column does not exist. It failed because those two spellings are not the same name. Inside double quotes an identifier is case-sensitive and is stored exactly as typed. Unquoted, an identifier is folded to upper case before anything else happens.

How you write it	The name Snowflake stores
AS GIVEN_NAME	GIVEN_NAME
AS given_name	GIVEN_NAME — folded to upper case
AS "Given Name"	Given Name — exactly as typed, space and all
AS "GIVEN_NAME"	GIVEN_NAME — the quotes are harmless here

The rule that follows from it

Once you quote an alias you have to quote it everywhere, spelled identically. So use plain unquoted aliases for anything you will write more SQL against, and save the pretty quoted ones for the final output a person reads. Episode 12 covers quoted identifiers properly — it is the reason beginners get zero rows from a query that looks perfectly correct.

Can we use an alias again later in the query?

Let's start with the one that is familiar from episode 7. An alias in ORDER BY works the way we would hope.

Query

SELECT FIRST_NAME, SALARY / 12 AS MONTHLY FROM EMPLOYEES ORDER BY MONTHLY DESC;

Result8 rows · 2 columns

FIRST_NAME	MONTHLY
Daniel	10916.66666667
Aisha	9833.33333333
Lena	8666.66666667
Marcus	8041.66666667
Priya	8041.66666667
Grace	7666.66666667
Tomas	7270.83333333
Sofia	5950.00000000

Now the part of this post worth staying for. The same alias, this time in a WHERE clause.

If you learned SQL anywhere else you have been told this cannot work, and the reasoning is good. WHERE is evaluated before the select list is built, so the alias does not exist yet. That is true in standard SQL and in a lot of databases.

Let's run it anyway.

Query — the one that supposedly cannot run

SELECT FIRST_NAME, SALARY / 12 AS MONTHLY FROM EMPLOYEES WHERE MONTHLY > 8000 ORDER BY MONTHLY DESC;

Result5 rows · 2 columns

FIRST_NAME	MONTHLY
Daniel	10916.66666667
Aisha	9833.33333333
Lena	8666.66666667
Marcus	8041.66666667
Priya	8041.66666667

It runs. Five rows, sorted, no error and no warning.

How this one was found

This page exists because every query in this course is executed against a real account before it is published. That query was written up as the one that fails, with the explanation above as the reason — and the capture came back successful. The account overruled the rule.

The documentation supports it: “In many cases, when you use a column alias for an expression (i.e. expr AS col_alias) in other parts of the same query (in JOIN, FROM, WHERE, GROUP BY, other column expressions, etc.), the expression is evaluated only once.”

Now let's write the same filter the portable way

If the query has to run somewhere other than Snowflake, we repeat the expression in the WHERE clause instead of naming the alias. More typing, same answer, works everywhere.

Query — standard SQL, expression written twice

SELECT FIRST_NAME, SALARY / 12 AS MONTHLY FROM EMPLOYEES WHERE SALARY / 12 > 8000;

Result5 rows · 2 columns

FIRST_NAME	MONTHLY
Aisha	9833.33333333
Marcus	8041.66666667
Lena	8666.66666667
Priya	8041.66666667
Daniel	10916.66666667

Five rows again, and the same five people. This version was run without an ORDER BY, so they arrive in a different order. What separates the two queries is not the answer. It is which of them we could paste into another database.

The documented caveat nobody mentions

Read the end of that quotation again: the expression is evaluated only once in many cases, which means sometimes it is not, and the docs go on to say the alias can then hold different values in different parts of the same query. Harmless for SALARY / 12. Not harmless at all for RANDOM() or CURRENT_TIMESTAMP() — do not lean on an alias whose expression is not deterministic.

Now let's build an alias out of another alias

That quotation ends with “other column expressions”, and that phrase is doing real work. It means one item of a select list can use the alias of another item in the same select list.

First let's see what we do without it. Here are two calculated columns that both start from SALARY, so SALARY is written out in both of them.

Query

SELECT FIRST_NAME, SALARY / 12 AS MONTHLY, SALARY / 52 AS WEEKLY FROM EMPLOYEES;

Result8 rows · 3 columns

FIRST_NAME	MONTHLY	WEEKLY
Aisha	9833.33333333	2269.23076923
Marcus	8041.66666667	1855.76923077
Lena	8666.66666667	2000.00000000
Tomas	7270.83333333	1677.88461538
Priya	8041.66666667	1855.76923077
Daniel	10916.66666667	2519.23076923
Sofia	5950.00000000	1373.07692308
Grace	7666.66666667	1769.23076923

Now let's build the second column from the name the first one was given.

Query

SELECT FIRST_NAME, SALARY / 12 AS MONTHLY, MONTHLY * 2 AS DOUBLE_PAY FROM EMPLOYEES ORDER BY MONTHLY DESC;

Result8 rows · 3 columns

FIRST_NAME	MONTHLY	DOUBLE_PAY
Daniel	10916.66666667	21833.33333334
Aisha	9833.33333333	19666.66666666
Lena	8666.66666667	17333.33333334
Marcus	8041.66666667	16083.33333334
Priya	8041.66666667	16083.33333334
Grace	7666.66666667	15333.33333334
Tomas	7270.83333333	14541.66666666
Sofia	5950.00000000	11900.00000000

DOUBLE_PAY is defined in terms of MONTHLY, one item to its left, and the division is written once.

And it chains as far as we want to take it.

Query

SELECT FIRST_NAME, SALARY / 12 AS MONTHLY, MONTHLY * 2 AS DOUBLE_PAY, DOUBLE_PAY + 100 AS PLUS_100 FROM EMPLOYEES ORDER BY MONTHLY DESC;

Result8 rows · 4 columns

FIRST_NAME	MONTHLY	DOUBLE_PAY	PLUS_100
Daniel	10916.66666667	21833.33333334	21933.33333334
Aisha	9833.33333333	19666.66666666	19766.66666666
Lena	8666.66666667	17333.33333334	17433.33333334
Marcus	8041.66666667	16083.33333334	16183.33333334
Priya	8041.66666667	16083.33333334	16183.33333334
Grace	7666.66666667	15333.33333334	15433.33333334
Tomas	7270.83333333	14541.66666666	14641.66666666
Sofia	5950.00000000	11900.00000000	12000.00000000

PLUS_100 is built from DOUBLE_PAY, which was built from MONTHLY. So the practical rule for Snowflake turns out to be much simpler than the one most courses teach. Name a thing once, then use the name.

Portability

A query written this way is using a Snowflake feature rather than standard SQL. It is the right thing to write for Snowflake, and it is worth knowing it may not survive a move to another database.

What if our alias has the same name as a real column?

That rule leaves one obvious question open. What happens when our alias has the same name as a column that already exists? Here SALARY * 2 is aliased as SALARY, and then the WHERE clause asks about SALARY.

Query

SELECT FIRST_NAME, SALARY * 2 AS SALARY FROM EMPLOYEES WHERE SALARY > 100000 ORDER BY FIRST_NAME;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Aisha	236000.00
Daniel	262000.00
Lena	208000.00

Three rows. Now let's work out which SALARY the WHERE clause used. Those three are the people whose actual salary is over 100000. If the filter had used the doubled alias, every row in the table would have qualified. The lowest salary in this table, doubled, is still comfortably over 100000.

So the table column wins the name, and the alias applies only to what comes out. The values shown in that grid are the doubled ones. The row selection is not.

Do not do this

The documentation advises against reusing a column's name as an alias, and this is why. The query is legal, it returns rows, and the only clue that two different things share one name is that the numbers do not add up. Pick a different alias.

One last thing: tables get aliases too

This is the same idea pointed at a different object. A table can be given a short name, and then we can refer to its columns through that name.

Query

SELECT E.FIRST_NAME AS NAME, D.DEPT_NAME AS DEPARTMENT FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPT_ID = D.DEPT_ID;

Result7 rows · 2 columns

NAME	DEPARTMENT
Aisha	Engineering
Marcus	Engineering
Lena	Finance
Tomas	Finance
Priya	Marketing
Daniel	Engineering
Sofia	Marketing

E and D are table aliases, and that is what makes E.FIRST_NAME and D.DEPT_NAME possible in one query. Seven rows come back from a table of eight employees, because Grace has no DEPT_ID and so matches no department. That is a joining question rather than an aliasing one.

On that join

That query joins two tables by putting the condition in the WHERE clause. It works, and it is not how you should write a join today. Explicit JOIN ... ON syntax, and what happens to rows like Grace's, get the whole of Level 3.

Key points

• An alias renames a column in the output only. The table is untouched.
• Without an alias, the header of a calculated column is the text of the expression — SALARY / 12, UPPER(LAST_NAME).
• AS is optional. Write it anyway: without it, a missing comma turns a column into an alias and returns a wrong answer instead of an error.
• Double-quoted aliases can contain spaces, capitals and punctuation, and are stored exactly as typed. Unquoted identifiers are folded to upper case.
• Quote an alias once and you must quote it everywhere, spelled identically — otherwise you get invalid identifier.
• In Snowflake an alias can be used in WHERE, in ORDER BY, and in another item of the same select list, which chains as far as you like.
• The docs say such an expression is evaluated only once in many cases — so never alias a non-deterministic expression and then reuse the name.
• An alias that shadows a real column name does not win: the WHERE clause filtered on the table's SALARY, not the doubled alias.
• Tables take aliases too, which is what makes E.FIRST_NAME work.

Next in this series

In the next post we will look at operators: arithmetic, concatenation and precedence, including why 7 / 2 is not 3 and what division quietly does to the shape of our number.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above, including the failing one, which shows Snowflake's exact error text. The EMPLOYEES and DEPARTMENTS data is invented demo data. No performance or benchmark claims are made anywhere in this series. Quotations are from the Snowflake SELECT documentation.