Knowledge Drop

What is a derived table?

Userlevel 2

Last tested: Jun 29, 2021

In SQL, a derived table is a subquery that is used in the FROM clause of a query.


SELECT * FROM (SELECT a, b, c FROM table) derived_table ;;

In the above example, SELECT a, b, c FROM table is our derived table.

In Looker, derived tables follow this same concept; however Looker does not generate a subquery in the FROM clause of the main query. For non-persisted derived tables, known as ephemeral tables, Looker generates a derived table using a common table expression (CTE) or a temporary table. CTEs are used for dialects that support them, like Redshift and Snowflake. For dialects that do not support CTEs, like MySQL Aurora and MariaDB, Looker creates a temporary table on the database for derived tables. These CTEs or temporary tables are then used in the FROM parameter of the main query.

Example CTE:

With derived_table AS (SELECT a, b, c, FROM table)

SELECT * FROM derived_table ;;


Example Temp Table:


SELECT a, b, c, FROM table ;;

SELECT * FROM derived_table ;;



This content is subject to limited support.                




0 replies

Be the first to reply!