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.