Oracle Natural Join

What is Natural Join in Oracle?

• The join is based on all the columns in the two tables that have the same name and data types.
• The join creates, by using the NATURAL JOIN keywords.
• It selects rows from the two tables that have equal values in all matched columns.
• When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

SQL:1999 Syntax

SELECT table1.column, table2.column
FROM table1
NATURAL JOIN table2;

Where table1, table2 are the name of the tables participating in joining.

Example: Oracle Natural Joins

In this example, the LOCATIONS table is joined to the COUNTRY table by the country_id column, which is the only column of the same name in both tables. If other common columns were present, the join would have used them all.

Sample table: locations

Sample table: countries

SQL Code:

SQL> SELECT postal_code, city,
  2  region_id, country_name
  3  FROM locations
  4  NATURAL JOIN countries;

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.	   

Natural Joins with a WHERE Clause

You can implement additional restrictions on a natural join using a WHERE clause. In the previous example the LOCATIONS table was joined to the DEPARTMENT table by the COUNTRY_ID column, now you can limit the rows of output to those with a location_id greater than 2000.

SQL Code:

SQL> SELECT postal_code, city,
   2  region_id, country_name
   3  FROM locations
   4  NATURAL JOIN countries
   5  WHERE location_id>2000;

Sample Output:

POSTAL_CODE  CITY                            REGION_ID COUNTRY_NAME
------------ ------------------------------ ---------- -------------------
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

12 rows selected.       

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

NATURAL JOINS: SQL and other Relational Databases

• SQL NATURAL JOIN
• MySQL NATURAL JOIN
• SQLite NATURAL JOIN

Previous: NON-EQUIJOINS
Next: JOINS with ON Clause