Page 70 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 4: Advanced SQL

Notes
Figure 4.1: Range of Complex Query Types

UNION Queries

A SELECT statement can consist of several query blocks connected by UNION or UNION ALL
statements. Each individual SELECT statement returns a query result which is a set of rows
selected from a specified table or tables. The union of these query results is presented as a table
that consists of all rows appearing in one or more of the original query results.
If only the UNION statement is used, all duplicate rows are removed from the final set of rows.
In this case, the maximum size of a tuple in the query result is given by the following formula:
(SelectListItems +1)*2 + (SumListLengths) <= 4000
where,
SelectListItems is the number of items in the select list.

SumListLengths is the sum of the lengths of all the columns in the select list.
At compile time, SumKeyLengths is computed assuming columns of NULL and VARCHAR
contain no data. At run time, the actual data lengths are assumed.

If the UNION ALL operator is used, duplicates are not removed. Candidates for duplicate removal
are evaluated by comparing entire tuples, not just a single field. Only if two or more rows are
entirely alike are the duplicates removed. In the case of the UNION ALL operator, the maximum
size of a tuple in the query result is 3996 bytes, as it is for a non-UNION query expression. You
cannot use LONG columns in a UNION statement.

LOVELY PROFESSIONAL UNIVERSITY 63