You can execute a SQL UNION
or UNION ALL
operator in both SQL-based derived tables (DT) and persistent derived tables (PDT). The UNION
and UNION ALL
operators combine the result sets of two queries.
This example shows what a SQL-based DT looks like with a UNION
in LookML:
view: union_example { derived_table: { sql: SELECT 1 AS my_int FROM union_example1 UNION SELECT 2 AS my_int FROM union_example2 ;; } }
This results in a derived table that combines the results of both queries.
The difference between UNION
and UNION ALL
is that UNION ALL
does not remove duplicate rows. There are performance considerations to keep in mind when using UNION
vs. UNION ALL
, because the database server must do additional work to remove the duplicate rows. However, you usually do not want the duplicates.