USING

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 JOIN operations that have an explicit join clause.

Syntax

USING ( [ Simple-column-Name ]* )

SimpleColumnName

The name of a table column, as described in the Simple Column Name topic.

Using

The columns listed in the USING clause must be present in both of the 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 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);

See Also