QUERY Expression (query_expression) 

QUERY expressions (query_expression) are required to generate an unordered result table in a SELECT statement.

Syntax

<query_expression> ::= <query_term>
                     | <query_expression> UNION [ALL] <query_term>
                     | <query_expression> EXCEPT [ALL] <query_term>

<query_term>       ::= <query_primary>
                     | <query_term> INTERSECT [ALL] <query_primary>

<query_primary>    ::= <query_spec>
                     | (<query_expression>)

<query_spec>       ::= SELECT [<distinct_spec>] [<top_spec>] <select_column>,... <table_expression>

<top_spec>         ::= TOP <unsigned_integer>
                     | TOP <parameter_name>

Examples

SQL Tutorial, Selecting, Arranging and Renaming Columns, Set Operations: UNION, INTERSECT, EXCEPT

Explanation

A QUERY specification specifies a result table. The result table is generated from a temporary result table. The temporary result table is the result of the table expression.

If the QUERY expression consists of only one QUERY specification query_spec, the result of the expression is the unchanged result of the QUERY specification.

If a QUERY expression consists of more than one QUERY specification, the number of selected columns in all QUERY specifications of the QUERY expression must be the same. The respective i^th selected columns of the QUERY specifications must be comparable.

Column Type (select column)
Numeric columns	Are comparable. If all ith selected columns are numeric columns, the ith column of the result table is a numeric column.
Alphanumerical column, code attribute BYTE	Are comparable.
Alphanumeric columns, code attribute ASCII, UNICODE	Are comparable. Are also comparable with date, time, and timestamp values.
All ith columns are date values	The ith column of the result table is a date value.
All ith columns are time values	The ith column of the result table is a time value.
All ith columns are timestamp values	The ith column of the result table is a timestamp value.
Columns of the type BOOLEAN	Are comparable.
All ith columns are of the type BOOLEAN	The ith column of the result table is of the type BOOLEAN.
Columns of any other data type (not mentioned above)	The ith column of the result table is an alphanumeric column. Comparable columns with differing code attributes are converted.

If columns are comparable but have different lengths, the corresponding column of the result table has the maximum length of the underlying columns.

Column Names in the Result Table

The names of the result table columns are formed from the names of the selected columns of the first QUERY specification.

Let T1 be the left operand of UNION, EXCEPT, or INTERSECT (defined in query term). Let T2 be the right operand. Let R be the result of the operation on T1 and T2.

●     A row is a duplicate of another row if both have identical values in each column. NULL values are assumed to be identical. Special NULL values are assumed to be identical.

●     UNION: R contains all rows from T1 and T2.

●     EXCEPT: R contains all rows from T1 which have no duplicate rows in T2.

●     INTERSECT: R contains all rows from T1 which have a duplicate row in T2. A row from T2 can only be a duplicate row of exactly one row from T1. More than one row from T1 cannot have the same duplicate row in T2.

●     DISTINCT is implicitly assumed for the query expressions belonging to T1 and T2 if ALL is not specified. All duplicate rows are removed from R.

If parentheses are missing, then INTERSECT will be evaluated before UNION and EXCEPT. UNION and EXCEPT have the same precedence and will be evaluated from left to right in the case that parentheses are missing.

TOP

You can use the TOP syntax element to specify that only the first n lines of the result are to be output. Whole numbers between 0 and 2147483647 are permitted.

If you use a TOP syntax element and an ORDER clause in a select statement, the first n lines of all lines sorted by the ORDER clause are output. If you do not use an ORDER clause in the SELECT statement, any n lines are output.

If a QUERY expression query_expression consists of several QUERY specifications query_spec, the TOP syntax element must only be contained in the first QUERY specification.

If you use a ROWNO predicate or a LIMIT-clause, you cannot use the TOP syntax element.