Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere® Server - SQL Reference
  SQL Statements
UNION statement

Use this statement to combine the results of two or more select statements.

[ WITH temporary-views ] query-block
UNION [ ALL | DISTINCT ] query-block
[ ORDER BY [ integer | select-list-expression-name ] [ ASC | DESC ], ... ]
[ FOR XML xml-mode ]
[ OPTION( query-hint, ... ) ]

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value

option-name : identifier

option-value : hostvar (indicator allowed), string, identifier, or number

OPTION clause    This clause provides hints as to how to process the query. The following query hints are supported:

• MATERIALIZED VIEW OPTIMIZATION 'option-value'  Use the MATERIALIZED VIEW OPTIMIZATION clause to specify how the optimizer should make use of materialized views when processing the query. The specified option-value overrides the materialized_view_optimization database option for this query only. Possible values for option-value are the same values available for the materialized_view_optimization database option. See materialized_view_optimization option [database].

• FORCE OPTIMIZATION  When a query specification contains only simple queries (single-block, single-table queries that contain equality conditions in the WHERE clause that uniquely identify a specific row), it typically bypasses cost-based optimization during processing. In some cases you may want cost-based optimization to occur. For example, if you want materialized views to be considered during query processing, view matching must occur. However, view matching only occurs during cost-base optimization. If you want cost-based optimization to occur for a query, but your query specification contains only simple queries, specify the FORCE OPTIMIZATION option to ensure that the optimizer performs cost-based optimization on the query.

  Similarly, specifying the FORCE OPTIMIZATION option in a SELECT statement inside of a procedure forces the use of the optimizer for any call to the procedure. In this case, plans for the statement are not cached.
  For more information on simple queries and view matching, see Phases of query processing, and Improving performance with materialized views.

• option-name = option-value  Specify an option setting that takes precedence over any public or temporary option settings that are in effect, for this statement only. The supported options are:

  • isolation_level option [compatibility]
  • max_query_tasks option [database]
  • optimization_goal option [database]
  • optimization_level option [database]
  • optimization_workload option [database]

The results of several query blocks can be combined into a larger result using UNION. Each query-block must have the same number of items in the select list.

The results of UNION ALL are the combined results of the query blocks. The results of UNION are the same as UNION ALL, except that duplicate rows are eliminated. Eliminating duplicates requires extra processing, so UNION ALL should be used instead of UNION where possible. UNION DISTINCT is identical to UNION.

If corresponding items in two select lists have different data types, SQL Anywhere chooses a data type for the corresponding column in the result and automatically convert the columns in each query-block appropriately.

The first query block of the UNION is used to determine the names to be matched with the ORDER BY clause.

The column names displayed are the same column names that are displayed for the first query-block. An alternative way of customizing result set column names is to use the WITH clause on the query-block.

Must have SELECT permission for each query-block.

None.

• SELECT statement

• SQL/2003    Core feature.

List all distinct surnames of employees and customers.

SELECT Surname
FROM Employees
UNION
SELECT Surname
FROM Customers;