In the real world scenario, you may derive the some column from base table and instead of creating temporary table you may use that derived query in FROM clause of the SQL statement. These types of queries are called derived tables. In this article,we will check on the Netezza derived tables.
Netezza Derived Tables
A derived table is basically a subquery 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. Netezza derived tables are built dynamically using additional SELECT within the query. The Netezza derived table rows are stored in Netezza swap partition space and discarded as soon as execution of current query finishes.
Other kind of derived table is created using WITH clause in Netezza. The derived table created using WITH clause can be accessed at multiple locations in same SQL query statement.
Read:
- Netezza WITH clause and Examples
- How to resolve Netezza SPU Swap Partition error using system variables?
- nzsql commands and examples
- Netezza Correlated subqueries
Netezza Derived Table Example
Below is the example of Netezza 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.
Read:
- Netezza External Tables and Examples
- Importance of right Distribution key in Netezza
- IBM Netezza CREATE TABLE IF NOT EXISTS Syntax and Examples
- Generate Netezza Table DDL using nz_ddl_table
- IBM Netezza DROP TABLE IF EXISTS Syntax and Examples
- Netezza Hash functions
- Netezza Primary Key Constraint and Syntax