Oracle Natural Join

Last update on August 19 2022 21:51:21 (UTC/GMT +8 hours)

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

Previous: NON-EQUIJOINS
Next: JOINS with ON Clause