SQL Reference

Chapter 2 | Supported standards 12
UNION operator
The UNION operator combines the results of two or more SELECT statements into a single result.
The single result is all of the returned records from the SELECT statements. By default, duplicate
records are not returned. To return duplicate records, use the ALL keyword (UNION ALL). The
format is:
SELECT statement UNION [ALL] SELECT statement
When using the UNION operator, the select lists for each SELECT statement must have the same
number of column expressions, with the same data types, and must be specified in the same
order. For example:
SELECT last_name, salary, hire_date FROM emp UNION SELECT name, pay,
birth_date FROM person
This example has the same number of column expressions, and each column expression, in
order, has the same data type.
The following example is not valid because the data types of the column expressions are different
(SALARY from EMP has a different data type than LAST_NAME from RAISES). This example has
the same number of column expressions in each SELECT statement, but the expressions are not
in the same order by data type.
SELECT last_name, salary FROM emp UNION SELECT salary, last_name FROM
raises
ORDER BY clause
The ORDER BY clause indicates how the records are to be sorted. The format is:
ORDER BY {sort_expression [DESC | ASC]}, ...
sort_expression can be field names, expressions, or the positional number of the column
expression to use. The default is to perform an ascending (ASC) sort.
For example, to sort by last_name then by first_name, you could use either of the following
SELECT statements:
SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name,
first_name
or
SELECT emp_id, last_name, first_name FROM emp ORDER BY 2,3
In the second example, last_name is the second column expression following SELECT, so
ORDER BY 2 sorts by last_name.
Note FileMaker SQL uses the Unicode binary sort order, which is different from the
FileMaker
Pro sort order used with language sorting or with the default language-neutral sort
order.