Returns data from one or more tables
Format
query-expr [, UNION query-expr ]*
[ ORDER BY sort-spec [, sort-spec ]* ]
Remarks
Executes a query returning data from one or more tables. Refer to query-expr for details on the query expression (SELECT) clause.
The UNION operator allows multiple query expressions to be combined into a single table.
The optional ORDER BY sorts the combined result set. Columns referenced by the sort clauses must be present in the result set.
SELECT FullName, CurrentStateDesc, CurrentTime FROM CPointAlg UNION
SELECT FullName, StateDesc, "Time" FROM CDBPointSource
ORDER BY 3
Any number of queries may be combined and an ORDER BY may follow to sort the combined results.
By default duplicate results are removed, UNION may optionally be followed by ALL to retain duplicates:
SELECT FullName, CurrentStateDesc, CurrentTime FROM CPointAlg UNION ALL
SELECT FullName, StateDesc, "Time" FROM CDBPointSource
ORDER BY 3
Parentheses are not needed to remove any ambiguity over the other use of the UNION operator.
The UNION operator has the following restrictions:
- The quantity of columns must match and the types must be compatible.
- The CORRESPONDING BY clause, GROUP BY/HAVING and DISTINCT/TOP are not supported.
- You have to include a WHERE clause when querying historic tables, to restrict the amount of data that is being queried (see Requirements when Querying Historic Tables).
- Use of UNION in sub-queries is not supported, for Example:
SELECT ... FROM ... WHERE ... IN (SELECT ... FROM ... UNION SELECT ... FROM ...)