An intermediate table is a table created on the database to store temporary data that are used to calculate the final result set. These tables can either be 'permanent' or 'true temporary'.
Most major databases have allowed the creation of special tables that have characteristics that separate them from 'normal' or 'permanent' tables in the database (Temporary Tables in SQL Server, Volatile Tables in Teradata, Declared Global Temporary Tables in DB2, etc.).
Usually, the creation of these tables is not logged on the database logs (Journal) and is not indexed, with some exceptions. These tables are created with the intent that they exist only temporarily on the relational database and are dropped when the connection is closed. For the remainder of this document, these tables are referred to as 'true temporary tables'.
On any database that does not support 'true temporary tables', any table created by Strategy or any other process, will be 'real' or 'permanent', since they are considered as permanent by the relational database, even if they are used to store temporary results. So, it is considered an intermediate permanent table from the perspective of Strategy. For the remainder of this document, these tables are referred to as 'permanent tables'. The use of 'permanent tables' may be problematic, since their creation cause overhead on the database side.
Usually, database vendors do not use the term 'true temporary tables'. From the point of view of the relational database, a 'true temporary table' is just a 'temporary table.' It is sometimes useful to make a distinction because Strategy software runs against a range of databases, some of which support 'true temporary tables' and others do not.
On databases where 'true temporary tables' are supported, the Strategy SQL Generation Engine can be configured to create the table type of choice using the 'Intermediate Table Type' VLDB property.
A derived table is nothing but a nested 'select' statement. This type of query does not create any table on the database side and all the processing is performed on the database server's memory.
