ODBC and JDBC Guide

Chapter 7 | Supported standards 35
The SELECT statement can use a variety of clauses:
SELECT [DISTINCT] {* | column_expression [[AS] column_alias],...}
FROM table_name [table_alias], ...
[ WHERE expr1 rel_operator expr2 ]
[ GROUP BY {column_expression, ...} ]
[ HAVING expr1 rel_operator expr2 ]
[ UNION [ALL] (SELECT...) ]
[ ORDER BY {sort_expression [DESC | ASC]}, ... ]
[ FOR UPDATE [OF {column_expression, ...}] ]
Items in brackets are optional.
column_alias can be used to give the column a more descriptive name, or to abbreviate a longer
column name. For example, to assign the alias
department to the column dept:
SELECT dept AS department FROM emp
Field names can be prefixed with the table name or the table alias. For example, EMP.LAST_NAME
or E.LAST_NAME, where E is the alias for the table EMP.
The DISTINCT operator can precede the first column expression. This operator eliminates
duplicate rows from the result of a query. For example:
SELECT DISTINCT dept FROM emp
SQL clauses
The ODBC and JDBC client drivers provide support for the following SQL clauses.
Note If you attempt to retrieve data from a table with no columns, the SELECT statement returns
nothing.
FROM clause
The FROM clause indicates the tables that are used in the SELECT statement. The format is:
FROM table_name [table_alias] [, table_name [table_alias]]
table_name is the name of a table in the current database.
table_alias can be used to give the table a more descriptive name, to abbreviate a longer table
name, or to include the same table in the query more than once (for example, in self-joins).
Use this SQL clause To
FROM (page 35) Indicate which tables are used in the SELECT statement.
WHERE (page 36) Specify the conditions that records must meet to be retrieved (like a FileMaker Pro find
request).
GROUP BY (page 37) Specify 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 by returning one row for each group
(like a FileMaker Pro subsummary).
HAVING (page 37) Specify conditions for groups of records (for example, display only the departments that
have salaries totaling more than $200,000).
UNION (page 37) Combine the results of two or more SELECT statements into a single result.
ORDER BY (page 38) Indicate how the records are sorted
FOR UPDATE (page 38) To perform Positioned Updates or Positioned Deletes via SQL cursors