Snowflake SQL Tutorial 42 - Derived Tables in Snowflake - A Subquery in FROM Is a Table That Lives for One Query

Snowflake SQL · Episode 42

In this post we are going to cover derived tables in Snowflake.

A derived table is a subquery we write inside the FROM clause. We have put subqueries in a WHERE clause before, and there they give us back a value. In the FROM clause a subquery gives us back a table. It lives for the length of one query, it has to have a name, and the rest of the query treats it like any other table.

We will start with a query that fails, because that failure is the reason derived tables are worth learning. Then we will fix it two ways, run the inside on its own to see what it really returns, join to it, use two of them at once, and finish with the one pattern worth stealing today.

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

Episode 42 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 does not work
3. Now let's fix it, two ways
4. Now let's run the inside on its own
5. Now let's join to it
6. Can we use more than one?
7. Now let's give a computed value a name
8. So what is a derived table, really?

Let's create some sample data

Everything below runs against EMPLOYEES and DEPARTMENTS. The script creates both. It puts eight rows into EMPLOYEES and four into DEPARTMENTS.

Copy it into a Snowflake worksheet and run it once.

Sample data

-- The tables this episode queries. EMPLOYEES comes from Episode 1 and
-- DEPARTMENTS from Episode 24; both are reproduced here so you can paste this
-- into a worksheet and follow along. CUSTOMERS and ORDERS, used once near the
-- end, are the shared sample tables for this course.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

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

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

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

-- Watch the DEPT_ID column: 10, 20, 30 - and Grace, on the last line, whose
-- DEPT_ID is NULL. Her group is the one that disappears in section 4.
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');

The column to keep an eye on is DEPT_ID. Three departments have people in them, 10, 20 and 30. One person, Grace, has a NULL department.

DEPARTMENTS also holds 40, Operations, which nobody works in. Both of those oddities turn into visible rows later on.

One section near the end uses CUSTOMERS and ORDERS, the shared sample tables for this course, joined on CUSTOMER_ID.

Let's start with a query that does not work

Here is an ordinary question. Which departments have more than one person in them?

The obvious way to write it is to group by department and filter on the count. Let's try that.

Query — this one fails

SELECT DEPT_ID, COUNT(*) AS HEADCOUNT
FROM EMPLOYEES
WHERE COUNT(*) > 1
GROUP BY DEPT_ID;

Query failed — Snowflake returned an error

002035 (42601): SQL compilation error:
Invalid aggregate function in where clause [COUNT(*)]

That is not a typo and it is not a Snowflake quirk. It is the order the clauses run in.

WHERE runs before the grouping, on individual rows. When WHERE is looking at a row, COUNT(*) has not been worked out yet. There is no count in existence, so there is nothing to compare the number 1 against.

Here is the whole sequence, so you can see where WHERE sits in it.

Stage	What it sees	Is COUNT(*) available?
FROM	Rows from the table	No
WHERE	One row at a time	No — the grouping has not happened
GROUP BY	Rows collapsed into groups	This is where it is computed
HAVING	One row per group	Yes
SELECT	One row per group	Yes

Read that table top to bottom and the error stops being surprising. WHERE is simply too early in the sequence to ask a question about a group.

Now let's fix it, two ways

The first way is HAVING, which we covered in Episode 17. HAVING runs after the grouping, and by then the count exists.

Query

SELECT DEPT_ID, COUNT(*) AS HEADCOUNT
FROM EMPLOYEES
GROUP BY DEPT_ID
HAVING COUNT(*) > 1
ORDER BY DEPT_ID;

Result3 rows · 2 columns

DEPT_ID	HEADCOUNT
10	3
20	2
30	2

Three rows came back: departments 10, 20 and 30.

Now let's try the second way

This time we put the grouped query in brackets inside the FROM clause, give it a name, and filter it with an ordinary WHERE.

The outer query is no longer looking at EMPLOYEES. It is looking at a table whose rows are already one per department, with HEADCOUNT sitting there as a column like any other.

Query

SELECT *
FROM (SELECT DEPT_ID, COUNT(*) AS HEADCOUNT
      FROM EMPLOYEES
      GROUP BY DEPT_ID) D
WHERE D.HEADCOUNT > 1
ORDER BY D.DEPT_ID;

Result3 rows · 2 columns

DEPT_ID	HEADCOUNT
10	3
20	2
30	2

The same three rows. WHERE D.HEADCOUNT > 1 works here and failed above for one reason. By the time the outer query runs, the count is no longer an aggregate waiting to be computed. It is a value sitting in a column.

The one syntax rule

The alias is not optional. Drop the D and Snowflake has nothing to call the table, and nothing to qualify D.HEADCOUNT with. Every derived table in the FROM clause needs a name.

So which one should we write?

For this question, HAVING is the better answer. It is shorter and it says what it means.

The derived table is worth learning because it generalises and HAVING does not. Here is what each one can do.

	HAVING	Derived table
Filters on an aggregate	Yes	Yes
Filter can be any condition at all	Only conditions on the grouped result's aggregates and keys	Yes — the outer query is an ordinary query
You can join the grouped result to another table	No	Yes
You can group, filter, then aggregate again	No	Yes
Shortest thing to write for a simple count filter	Yes	No

Now let's run the inside on its own

The useful habit with any derived table is to run the inside by itself. It is a complete query. Nothing stops us executing it.

Here it is with nothing filtering it.

Query — the inside, alone

SELECT DEPT_ID, COUNT(*) AS HEADCOUNT
FROM EMPLOYEES
GROUP BY DEPT_ID
ORDER BY DEPT_ID NULLS LAST;

Result4 rows · 2 columns

DEPT_ID	HEADCOUNT
10	3
20	2
30	2
NULL	1

Four rows, not three. Departments 10, 20 and 30, and then a row whose DEPT_ID is NULL.

That last row is Grace, who has no department. GROUP BY puts every employee with a NULL department into one group, and her group has a headcount of 1.

So HEADCOUNT > 1 drops exactly that row. Four rows in, three rows out. Both grids are on this page, so the filter is not something we have to take on trust. Put this grid beside the HEADCOUNT grids above it and the missing row names itself.

Worth a habit

This is the debugging technique for derived tables generally. If the outer query gives you the wrong number of rows, select the bracketed query on its own and run it. The inside is never a mystery — it is a query you can execute.

Now let's join to it

A derived table is a table for the length of the query, so we can put it on either side of a join.

Here we join the per-department totals back to the real DEPARTMENTS table, which has the name and the budget that EMPLOYEES does not. Then we do some arithmetic across both sides, payroll as a percentage of budget.

Query

SELECT D.DEPT_ID,
       D.DEPT_NAME,
       D.BUDGET,
       S.HEADCOUNT,
       S.PAYROLL,
       ROUND(S.PAYROLL / D.BUDGET * 100, 1) AS PAYROLL_PCT_OF_BUDGET
FROM DEPARTMENTS D
JOIN (SELECT DEPT_ID, COUNT(*) AS HEADCOUNT, SUM(SALARY) AS PAYROLL
      FROM EMPLOYEES
      GROUP BY DEPT_ID) S
  ON S.DEPT_ID = D.DEPT_ID
ORDER BY D.DEPT_ID;

Result3 rows · 6 columns · scroll →

DEPT_ID	DEPT_NAME	BUDGET	HEADCOUNT	PAYROLL	PAYROLL_PCT_OF_BUDGET
10	Engineering	2400000.00	3	345500.00	14.4
20	Finance	1100000.00	2	191250.00	17.4
30	Marketing	750000.00	2	167900.00	22.4

Three rows and six columns. Some of those columns come from a table on disk. Some come from a table that only existed while the query ran. One, PAYROLL_PCT_OF_BUDGET, is computed from one of each. Nothing in the outer query knows or cares which side was which.

Now have a look at what is missing

There is no row for 40, Operations. Nobody works there, so Operations produces no group in the inner query. Look back at the four rows we just ran on their own and 40 is not among them.

It has no row in the derived table at all, and that was an inner join, so the department drops out of the result entirely.

Let's change one word and bring it back.

Query

SELECT D.DEPT_ID, D.DEPT_NAME, S.HEADCOUNT, S.PAYROLL
FROM DEPARTMENTS D
LEFT JOIN (SELECT DEPT_ID, COUNT(*) AS HEADCOUNT, SUM(SALARY) AS PAYROLL
           FROM EMPLOYEES
           GROUP BY DEPT_ID) S
  ON S.DEPT_ID = D.DEPT_ID
ORDER BY D.DEPT_ID;

Result4 rows · 4 columns

DEPT_ID	DEPT_NAME	HEADCOUNT	PAYROLL
10	Engineering	3	345500.00
20	Finance	2	191250.00
30	Marketing	2	167900.00
40	Operations	NULL	NULL

Four rows now. Operations is there with a NULL headcount and a NULL payroll. That is Episode 25's rule, unchanged: a NULL on the right-hand side means no matching row. The only thing different here is that the right-hand side is a table that does not exist on disk.

The reporting trap

A department with no employees is exactly the row somebody is looking for when they ask which departments are unstaffed. An inner join to a grouped derived table silently answers a narrower question than the one asked: it reports on the groups that exist, not the keys that exist.

Can we use more than one?

Yes. Nothing stops us. Here two derived tables are joined to CUSTOMERS, one counting orders and one totalling them.

Query

SELECT C.CUSTOMER_NAME, N.N_ORDERS, T.TOTAL
FROM CUSTOMERS C
JOIN (SELECT CUSTOMER_ID, COUNT(*) AS N_ORDERS FROM ORDERS GROUP BY CUSTOMER_ID) N
  ON N.CUSTOMER_ID = C.CUSTOMER_ID
JOIN (SELECT CUSTOMER_ID, SUM(AMOUNT) AS TOTAL FROM ORDERS GROUP BY CUSTOMER_ID) T
  ON T.CUSTOMER_ID = C.CUSTOMER_ID
ORDER BY T.TOTAL DESC;

Result8 rows · 3 columns

CUSTOMER_NAME	N_ORDERS	TOTAL
Northwind Traders	9	61660.00
Meridian Health	5	56800.00
Alpine Logistics	5	36565.00
Vantage Media	3	15870.00
Kestrel Analytics	5	15635.25
Harbour Foods	4	9076.25
Blue Ridge Supply	6	6060.50
Delta Print Co	4	3760.00

Eight rows, and the answer is correct. Now read the query text rather than the result. The two things it is actually doing are buried in the middle of the FROM clause, between the SELECT list that uses them and the ORDER BY that sorts them.

And can we put one inside another?

We can. This one groups, then filters the groups, then averages what survives. Three stages, and the one that runs first is written innermost.

Query

SELECT AVG(HEADCOUNT) AS AVG_HEADCOUNT_OF_BIG_DEPTS
FROM (SELECT *
      FROM (SELECT DEPT_ID, COUNT(*) AS HEADCOUNT
            FROM EMPLOYEES
            GROUP BY DEPT_ID) INNER_D
      WHERE INNER_D.HEADCOUNT > 1) OUTER_D;

Result1 row · 1 column

AVG_HEADCOUNT_OF_BIG_DEPTS
2.333333

One row and one column: the average headcount of the departments with more than one person in them.

We can check that value against a grid already on this page. The headcounts that survived the filter earlier were 3, 2 and 2, and this value is their mean.

It runs and it is correct. But at two levels deep it is already hard to read, because we have to start from the middle and work outwards. Three levels is worse, and real queries go deeper than three.

That is not a reason to avoid derived tables. It is a reason to want a way of writing the same thing in the order it happens, which is what the next post is about.

Now let's give a computed value a name

One more pattern, and this is the one worth stealing today. A derived table that returns exactly one row, CROSS JOINed in.

That gives a computed value a name, and we write the computation once.

Query

SELECT E.FIRST_NAME, E.SALARY, B.BAR, E.SALARY - B.BAR AS ABOVE_BAR
FROM EMPLOYEES E
CROSS JOIN (SELECT AVG(SALARY) AS BAR FROM EMPLOYEES) B
WHERE E.SALARY > B.BAR
ORDER BY ABOVE_BAR DESC;

Result3 rows · 4 columns

FIRST_NAME	SALARY	BAR	ABOVE_BAR
Daniel	131000.00	99581.25000000	31418.75000000
Aisha	118000.00	99581.25000000	18418.75000000
Lena	104000.00	99581.25000000	4418.75000000

BAR is the company average salary. It is computed once in the brackets and then available everywhere in the outer query: in the WHERE clause that filters, and in the SELECT list that subtracts it.

Three rows came back, each carrying the bar and its own distance above it.

The CROSS JOIN looks alarming and it is not. One row against eight rows is eight rows. It attaches the same single value to every employee, which is precisely what we want. The WHERE clause then keeps the ones above the bar.

Why it is worth the brackets

Without this you would write the AVG(SALARY) subquery twice: once in the WHERE clause and once in the SELECT list. Two copies of the same expression is two things to keep in step, and the day somebody edits one of them the query starts comparing against a different number than it reports.

So what is a derived table, really?

Strip away the examples and there is very little to it. The FROM clause wants something that produces rows and columns. A table produces rows and columns. So does a SELECT statement, so we can write one there, in brackets, with a name.

Here is everything we have seen on this page, in one list.

• It exists for the length of one query and then it is gone. Nothing is created, nothing is stored, nothing needs cleaning up.
• It must have an alias, because the outer query needs a name to refer to it and to qualify its columns with.
• Its columns are whatever its SELECT list produces, aliases included — which is how COUNT(*) becomes a plain column called HEADCOUNT that an ordinary WHERE can filter.
• It can be joined, left-joined and cross-joined like any table, and the usual outer-join rules apply to it unchanged.
• It can contain another one, as deep as you like, and that is where readability goes.

That last point is the only real argument against them, and it is an argument about writing rather than about correctness. Snowflake has a construct that takes the same stages and lets us name them in the order they run, top to bottom. That is the next post.

Key points

• WHERE runs before the grouping, so it cannot see COUNT(*) — Snowflake rejects the query with Invalid aggregate function in where clause rather than guessing what you meant.
• A subquery in the FROM clause is a table that exists for the length of one query. Its aggregates are already computed, so an ordinary WHERE can filter them.
• The alias is not optional. Snowflake needs a name for the table and for qualifying its columns.
• For a simple filter on a count, HAVING is the better answer — shorter, and it says what it means. The derived table earns its place by generalising: you can join to it, and you can aggregate it again.
• Run the bracketed query on its own when the row count surprises you. It is a complete query, and the group that the outer filter removed is visible the moment you execute the inside.
• A grouped derived table has no row for a key with no rows behind it, so an inner join to one quietly drops that key. A LEFT JOIN brings it back with NULL measures.
• A one-row derived table CROSS JOINed in gives a computed value a name you can use in both the WHERE clause and the SELECT list, written once.
• Nesting works and stays correct, but you have to read it from the inside out. Two levels is already awkward; that is a readability problem, not a correctness one.

Next in this series

Episode 43 — WITH and common table expressions. The same queries as this post, with the stages lifted out of the FROM clause, given names, and read top to bottom instead of inside out.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the compilation error in section 2. The EMPLOYEES, DEPARTMENTS, CUSTOMERS and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Clause behaviour follows the Snowflake FROM clause documentation.