ODBC and JDBC Guide

38 FileMaker ODBC and JDBC Guide
The WHERE clause can also use expressions such as these:
WHERE expr1 IS NULL
WHERE NOT expr2
Note If you use fully qualified names in the SELECT (projection) list, you must also use fully qualified
names in the related WHERE clause.
GROUP BY clause
The GROUP BY clause specifies the names of one or more fields by which the returned values should be
grouped. This clause is used to return a set of aggregate values. It has the following format:
GROUP BY columns
columns must match the column expression used in the SELECT clause. A column expression can be one
or more field names of the database table separated by commas.
The following example sums the salaries in each department.
SELECT dept_id, SUM (salary) FROM emp GROUP BY dept_id
This statement returns one row for each distinct department ID. Each row contains the department ID and
the sum of the salaries of the employees in the department.
HAVING clause
The HAVING clause enables you to specify conditions for groups of records (for example, display only the
departments that have salaries totaling more than $200,000). It has the following format:
HAVING expr1 rel_operator expr2
expr1 and expr2 can be field names, constant values, or expressions. These expressions do not have to
match a column expression in the SELECT clause.
rel_operator is the relational operator that links the two expressions. The following example returns only
the departments whose sums of salaries are greater than $200,000:
SELECT dept_id, SUM (salary) FROM emp
GROUP BY dept_id HAVING SUM (salary) > 200000
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