In a data warehouse environment, there are many places where you need to derive tables to meet certain requirements such as calculating columns, renaming table columns etc. You can use derived tables in place of temporary tables. In this article, we will check Vertica derived tables and how to use them in SQL queries.
Vertica Derived Table
A derived table in Vertica is basically a sub-query which is always in the FROM clause of a SQL query Statements. The reason it is called a derived table is because it essentially functions as a table as far as the entire query is concerned.
Other kind of derived table is created using WITH clause in Verica. The derived table created using WITH clause can be accessed at multiple locations in same SQL query statement.
Vertica Derived Table in from Example
As mentioned earlier, we derive tables usually in FROM clause of the SQL query.
Below is the simple example of Vertica derived tables. Any query that is available in FROM clause is derived table.
select max(age)
from ( --this part of the query is a derived table
select age from table ) as Age -- derived table have an alias name.Vertica Derived Table in WITH Clause Example
WTH Clause is an optional clause that always precedes SELECT clause in the query statements. In Vertica, you can use WITH clauses to simplify complicated queries and reduce statement or logic repetition.
Vertica WITH clause is very useful when you are working on the query that has multiple repeat and complex logic in it.
Below is the simple example of Vertica WITH clause:
WITH... with-query-1 [(col-name[,…])]AS (SELECT…),
with-query-2 [(col-name[,…])]AS (SELECT… [with-query-1]),
.
.
with-query-n [(col-name[,…])]AS (SELECT… [with-query-1, with-query-2, with-query-n[,…]])
<query expression body>;Related Reading