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 ;;
This content is subject to limited support.