ODBC and JDBC Developer’s Guide

24 FileMaker ODBC and JDBC Developer’s Guide
SELECT statement
Use the SELECT statement to specify which columns you're requesting. Follow the SELECT statement with
the column expressions (similar to field names) you want to retrieve (for example, last_name). Expressions
can include mathematical operations or string manipulation (for example,
SALARY * 1.05).
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.
Note SELECT * on larger databases and SELECT statements that use table aliases or literals in the projection
list might not function correctly. To avoid potential confusion, avoid wildcards and specify table and column
names without aliases.
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
Note If you attempt to retrieve data from a table with no columns, the SELECT statement fails.
Use this SQL clause To
FROM Indicate which tables are used in the SELECT statement.
WHERE Specify the conditions that records must meet to be retrieved (like a FileMaker Pro find
request).
GROUP BY 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 Specify conditions for groups of records (for example, display only the departments that
have salaries totaling more than $200,000). This clause is only valid if you have already
defined a GROUP BY clause.
UNION Combine the results of two or more SELECT statements into a single result.
ORDER BY Indicate how the records are sorted
FOR UPDATE Lock the records of the database table selected by the SELECT statement