background image
<< The result offset and fetch first clauses | WHERE CURRENT OF clause >>

USING clause

<< The result offset and fetch first clauses | WHERE CURRENT OF clause >>
Derby Reference Manual
86
p.setInt(1, 100);
ResultSet rs = p.executeQuery();
Note: Make sure to specify the ORDER BY clause if you expect to retrieve a sorted
result set. If you do not use an ORDER BY clause, the result set that is retrieved will
typically have the order in which the records were inserted.
USING clause
The USING clause specifies which columns to test for equality when two tables are
joined. It can be used instead of an ON clause in the
JOIN operations
that have an
explicit join clause.
Syntax
USING (
Simple-column-Name
[ ,
Simple-column-Name
]* )
The columns listed in the USING clause must be present in both of the two tables being
joined. The USING clause will be transformed to an ON clause that checks for equality
between the named columns in the two tables.
When a USING clause is specified, an asterisk (*) in the select list of the query will be
expanded to the following list of columns (in this order):
· All the columns in the USING clause
· All the columns of the first (left) table that are not specified in the USING clause
· All the columns of the second (right) table that are not specified in the USING
clause
An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to
every column of that table that is not listed in the USING clause.
If a column in the USING clause is referenced without being qualified by a table name,
the column reference points to the column in the first (left) table if the join is an INNER
JOIN or a LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a
column in the USING clause point to the column in the second (right) table.
Examples
The following query performs an inner join between the COUNTRIES table
and the CITIES table on the condition that COUNTRIES.COUNTRY is equal to
CITIES.COUNTRY:
SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY)
The next query is similar to the one above, but it has the additional join condition that
COUNTRIES.COUNTRY_ISO_CODE is equal to CITIES.COUNTRY_ISO_CODE:
SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY, COUNTRY_ISO_CODE)
WHERE clause
A WHERE clause is an optional part of a
SelectExpression
,
DELETE statement
, or
UPDATE statement
. The WHERE clause lets you select rows based on a boolean
expression. Only rows for which the expression evaluates to TRUE are returned in the
result, or, in the case of a DELETE statement, deleted, or, in the case of an UPDATE
statement, updated.
Syntax
WHERE
Boolean expression