Difference between Derived Columns, Derived Tables and SUBQUERIES

There are various kinds of TEMPORARY solution in any RDBMS system. It is interesting to know the difference between them and in any SQL Query , there can be DERIVED COLUMNS, DERIVED TABLES and there can be SUBQUERIES. There is huge difference between all the three and their applications. Below is the main differences between the DERIVED COLUMNS , DERIVED TABLES and SUBQUERIES.

a) Derived columns are used in Select clause ; Derived Tables are used in FROM clause and SUBQUERIES are used in WHERE clause.

b) Derived columns are calculated for each row ; Derived Tables are calculated once in SQL Statement and then is used in the SQL block, SUBQUERIES can be calculated for each row as well as once in SQL Statement.

c) Derived columns need permanent space if we are storing it in the table, Derived Tables and SubQueries use only Spool Space and no space is utilised in Data Dictionary.
d) Derived Tables need exclusive column names in SELECT clause however no such requirement in Derived Columns or Subqueries

If you can think of any more differences between these three , feel free to leave a comment in order to share the knowledge.

Leave a Reply

Your email address will not be published. Required fields are marked *