command-query

Use this command to return 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.

Example:

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 ...)

Required Permissions

To successfully use this command, the User Account of the user that is running the query has to be assigned the Read permission on every database item that is being queried. Database items for which the User Account is not assigned the Read permission are excluded from the result set. Individual fields that the user does not have the required permission to read (see User Access Considerations when Working with SQL) will return an 'empty' value for those fields.

For further information, see User Access Considerations when Working with SQL.