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
Previous:
NON-EQUIJOINS
Next:
JOINS with ON Clause