database concepts

Advanced SQL : advanced querying

Derived Tables

Sub-queries are not limited to inclusion in the WHERE clause. They can also be used in the FROM clause, creating a temporary derived table that is used in the query.

Creating a derived table that has an aggregate value in it such as MAX, AVG or MIN, allows the aggregate to be used in the WHERE clause.

UNION Summary
The UNION of two tables consists of all rows that are in one or both tables. The duplicate rows are eliminated from the resulting table. The resulting table does not contain two rows with identical data values. There is a basic requirement to perform a union operation on two tables:

• Both tables must have the same degree, that is the number of columns must be the same
• The domains of the corresponding columns in the two tables must be the same, that is the datatype of corresponding columns is the same.

Or to put it simply: The UNION operator takes output from two queries and returns all rows from both results. Any duplicate rows are displayed only once.

Unlike SELECT queries which do not eliminate duplicates from the results, UNION does eliminate duplicates.