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_IDFIRST_NAMELAST_NAMEEMAILDEPT_IDMANAGER_IDSALARYCOMMISSION_PCTHIRE_DATE
1AishaKhanaisha.khan@example.com106118000.00NULL2019-03-11
2MarcusReidmarcus.reid@example.com10696500.00NULL2021-07-01
3LenaFischerlena.fischer@example.com206104000.000.052018-01-22
4TomasNovaktomas.novak@example.com20387250.00NULL2022-09-05
5PriyaRamanpriya.raman@example.com30696500.000.102020-11-17
6DanielO'Connordaniel.oconnor@example.com10NULL131000.00NULL2016-05-30
7SofiaMarinosofia.marino@example.com30571400.000.082023-02-13
8GraceLingrace.lin@example.comNULL692000.00NULL2021-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_NAMELAST_NAMESALARY
AishaKhan118000.00
MarcusReid96500.00
LenaFischer104000.00
TomasNovak87250.00
PriyaRaman96500.00
DanielO'Connor131000.00
SofiaMarino71400.00
GraceLin92000.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
SALARYFIRST_NAMELAST_NAME
118000.00AishaKhan
96500.00MarcusReid
104000.00LenaFischer
87250.00TomasNovak
96500.00PriyaRaman
131000.00DanielO'Connor
71400.00SofiaMarino
92000.00GraceLin

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_NAMESALARY / 12UPPER(LAST_NAME)
Aisha9833.33333333KHAN
Marcus8041.66666667REID
Lena8666.66666667FISCHER
Tomas7270.83333333NOVAK
Priya8041.66666667RAMAN
Daniel10916.66666667O'CONNOR
Sofia5950.00000000MARINO
Grace7666.66666667LIN

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_NAMEMONTHLY_PAYSURNAME
Aisha9833.33333333KHAN
Marcus8041.66666667REID
Lena8666.66666667FISCHER
Tomas7270.83333333NOVAK
Priya8041.66666667RAMAN
Daniel10916.66666667O'CONNOR
Sofia5950.00000000MARINO
Grace7666.66666667LIN

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_IDFIRST_NAMELAST_NAMEEMAILDEPT_IDMANAGER_IDSALARYCOMMISSION_PCTHIRE_DATE
1AishaKhanaisha.khan@example.com106118000.00NULL2019-03-11
2MarcusReidmarcus.reid@example.com10696500.00NULL2021-07-01
3LenaFischerlena.fischer@example.com206104000.000.052018-01-22
4TomasNovaktomas.novak@example.com20387250.00NULL2022-09-05
5PriyaRamanpriya.raman@example.com30696500.000.102020-11-17
6DanielO'Connordaniel.oconnor@example.com10NULL131000.00NULL2016-05-30
7SofiaMarinosofia.marino@example.com30571400.000.082023-02-13
8GraceLingrace.lin@example.comNULL692000.00NULL2021-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_IDFIRST_NAMELAST_NAMEDEPT_IDMANAGER_IDCOMMISSION_PCTHIRE_DATE
1AishaKhan106NULL2019-03-11
2MarcusReid106NULL2021-07-01
3LenaFischer2060.052018-01-22
4TomasNovak203NULL2022-09-05
5PriyaRaman3060.102020-11-17
6DanielO'Connor10NULLNULL2016-05-30
7SofiaMarino3050.082023-02-13
8GraceLinNULL6NULL2021-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_NUMBERFIRST_NAMELAST_NAMEEMAILDEPT_IDMANAGER_IDSALARYCOMMISSION_PCTHIRE_DATE
1AishaKhanaisha.khan@example.com106118000.00NULL2019-03-11
2MarcusReidmarcus.reid@example.com10696500.00NULL2021-07-01
3LenaFischerlena.fischer@example.com206104000.000.052018-01-22
4TomasNovaktomas.novak@example.com20387250.00NULL2022-09-05
5PriyaRamanpriya.raman@example.com30696500.000.102020-11-17
6DanielO'Connordaniel.oconnor@example.com10NULL131000.00NULL2016-05-30
7SofiaMarinosofia.marino@example.com30571400.000.082023-02-13
8GraceLingrace.lin@example.comNULL692000.00NULL2021-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_IDFIRST_NAMELAST_NAMEEMAILDEPT_IDMANAGER_IDSALARYCOMMISSION_PCTHIRE_DATE
1AishaKhanaisha.khan@example.com106118000NULL2019-03-11
2MarcusReidmarcus.reid@example.com10697000NULL2021-07-01
3LenaFischerlena.fischer@example.com2061040000.052018-01-22
4TomasNovaktomas.novak@example.com20387000NULL2022-09-05
5PriyaRamanpriya.raman@example.com306970000.102020-11-17
6DanielO'Connordaniel.oconnor@example.com10NULL131000NULL2016-05-30
7SofiaMarinosofia.marino@example.com305710000.082023-02-13
8GraceLingrace.lin@example.comNULL692000NULL2021-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_IDFIRST_NAMELAST_NAMEEMAILDEPT_IDMANAGER_IDSALARYCOMMISSION_PCTHIRE_DATE
1AishaKhanaisha.khan@example.com106118000.00NULL2019-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_NAMELAST_NAME
AishaKhan
MarcusReid
LenaFischer
TomasNovak
PriyaRaman
DanielO'Connor
SofiaMarino
GraceLin

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 combinationWhat it does
EXCLUDE + REPLACEDrop some columns, change the values of others
EXCLUDE + RENAMEDrop some columns, rename others
EXCLUDE + REPLACE + RENAMEAll three, written in that order
ILIKE + REPLACEPick columns by name, change their values
ILIKE + RENAMEPick columns by name, rename them
ILIKE + REPLACE + RENAMEAll three, written in that order
REPLACE + RENAMEChange 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 →
IDFIRST_NAMELAST_NAMEDEPT_IDMANAGER_IDSALARYCOMMISSION_PCTHIRE_DATE
1AishaKhan106118000.00NULL2019-03-11
2MarcusReid10696500.00NULL2021-07-01
3LenaFischer206104000.000.052018-01-22
4TomasNovak20387250.00NULL2022-09-05
5PriyaRaman30696500.000.102020-11-17
6DanielO'Connor10NULL131000.00NULL2016-05-30
7SofiaMarino30571400.000.082023-02-13
8GraceLinNULL692000.00NULL2021-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_NAMESALARY
Aisha118000.00
Marcus96500.00
Lena104000.00
Tomas87250.00
Priya96500.00
Daniel131000.00
Sofia71400.00
Grace92000.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.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)