Oracle: Creating Joins with the USING Clause

How to create a join with the USING clause in Oracle?

• Use the USING clause to specify the columns for the equijoin where several columns have the same names but not same data types.
• Use the USING clause to match only one column when more than one column matches.
• The NATURAL JOIN and USING clauses are mutually exclusive.

Syntax:

SELECT table1.column, table2.column
FROM table1
JOIN table2 USING (join_column1, join_column2…);

Explanation:

• table1, table2 are the name of the tables participating in joining.
• The natural join syntax contains the NATURAL keyword, the JOIN…USING syntax does not.
• An error occurs if the NATURAL and USING keywords occur in the same join clause.
• The JOIN…USING clause allows one or more equijoin columns to specify in brackets after the USING keyword.

Example: Creating Joins with the USING clause in Oracle

In this example, the LOCATIONS table is joined to the COUNTRY table by the country_id column (only column of the same name in both tables).

Sample table: locations

Sample table: countries

SQL> SELECT location_id, postal_code, country_name
  2  FROM locations
  3  JOIN countries
  4  USING (country_id);

Sample Output:

POSTAL_CODE  CITY                            REGION_ID COUNTRY_NAME
------------ ------------------------------ ---------- -------------------------
00989        Roma                                    1 Italy
10934        Venice                                  1 Italy
1689         Tokyo                                   3 Japan
6823         Hiroshima                               3 Japan
26192        Southlake                               2 United States of America
99236        South San Francisco                     2 United States of America
50090        South Brunswick                         2 United States of America
98199        Seattle                                 2 United States of America
M5V 2L7      Toronto                                 2 Canada
YSW 9T2      Whitehorse                              2 Canada
190518       Beijing                                 3 China
490231       Bombay                                  3 India
2901         Sydney                                  3 Australia
540198       Singapore                               3 Singapore
             London                                  1 United Kingdom
OX9 9ZB      Oxford                                  1 United Kingdom
09629850293  Stretford                               1 United Kingdom
80925        Munich                                  1 Germany
01307-002    Sao Paulo                               2 Brazil
1730         Geneva                                  1 Switzerland
3095         Bern                                    1 Switzerland
3029SK       Utrecht                                 1 Netherlands
11932        Mexico City                             2 Mexico

23 rows selected.	   

Using Table Aliases with the USING Clause

When we use the USING clause in a join statement, the join column is not qualified with table aliases. Do not alias it even if the same column is used elsewhere in the SQL statement. See the following example:

SQL Code:

 SQL> SELECT l.location_id, l.street_address, l.postal_code, c.country_name
   2  FROM locations l  
   3  JOIN countries c  
   4  USING (country_id)  
   5  WHERE c.country_id<>'IT';

Sample Output:

WHERE c.country_id<>'IT'
      *
ERROR at line 5:
ORA-25154: column part of USING clause cannot have qualifier
 

Note: The columns that are common in both the tables, but not used in the USING clause, must be prefixed with a table alias.

Outputs of the said SQL statement shown here is taken by using Oracle Database 11g Express Edition.

Previous: JOINS with ON Clause
Next: INNER JOINS