What is a derived table? (Community)

View the original community article here

Last tested: Jun 29, 2021
 

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

Example:

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:

CREATE TEMPORARY TABLE derived_table

SELECT a, b, c, FROM table ;;

SELECT * FROM derived_table ;;