Show TOC

QUERY Expression (query_expression)Locate this document in the navigation structure

Use

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

Structure
				
<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 QUERY 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 i th selected columns are numeric columns, the i th column of the result table is a numeric column.

Alphanumerical column, code attribute BYTE

Are comparable.

Alphanumerical column, code attribute ASCII, UNICODE

Are comparable. Are also comparable with date, time, and time stamp values.

All i th columns are date values

The i th column of the result table is a date value.

All i th columns are time values

The i th column of the result table is a time value.

All i th columns are time stamp values

The i th column of the result table is a time stamp value.

Columns of the type BOOLEAN

Are comparable.

All i th columns are of the type BOOLEAN

The i th column of the result table is of the type BOOLEAN.

Columns of any other data type (not mentioned above)

The i th 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 that have no duplicate rows in T2.

  • INTERSECT: R contains all rows from T1 that have one 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 priority and will be evaluated from left to right if 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 generated. 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 generated. If you do not use an ORDER clause in the SELECT statement, any n  lines are generated.

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, a LIMIT-clause or a CREATE VIEW statement, you cannot use the TOP syntax element.