ODBC and JDBC Developer’s Guide

Supported standards 31
If you are joining more than one table, and you want to discard all rows that don’t have corresponding rows in
both source tables, you can use INNER JOIN. For example:
SELECT *
FROM Salespeople INNER JOIN Sales_Data
ON Salespeople.Salesperson_ID = Sales_Data.Salesperson_ID
Note OUTER JOIN is not currently supported.
WHERE clause
The WHERE clause specifies the conditions that records must meet to be retrieved. The WHERE clause
contains conditions in the form:
WHERE expr1 rel_operator expr2
expr1 and expr2 can be field names, constant values, or expressions.
rel_operator is the relational operator that links the two expressions. For example, the following SELECT
statement retrieves the names of employees who make $20,000 or more.
SELECT last_name,first_name FROM emp WHERE salary >= 20000
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 column_expressions
column_expressions 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, or one or more expressions
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). This clause is valid only if you have already
defined a GROUP BY clause. 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