Definition of Natural Joins:
A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns of the two tables that are being joined. Common columns are columns that have the same name in both the tables.
A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. However the INNER join is the default one.
Source: Oracle

We will start with the Natural Join. But before jumping over to the natural join let me tell you a few terminologies.

Source table: The table which comes after FROM clause in the select statement. 

Target table: All the tables that come after JOIN clause in the query.

When the join query is executed then the oracle starts matching the data from the source table to the target table. If there is a hit for a matching source table data in the target table then the value is returned. 

Examples of Natural Joins in Oracle Database

Scenario 1: When there is only one identical name column between source and target tables. 

Let’s use Departments table and locations table for the demonstration. 

Department table has 4 columns – DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID

And Locations table has 6 columns – LOCATION_ID, STREET ADDRESS, POSTAL CODE, CITY, STATE PROVINCE AND COUNTRY_ID

Upon minutely observing you will notice that location id is a common column between our source and target tables. It is a foreign key and thus has the same data type and column width. Let’s write the query for natural join. 

Say you want to find the City for all your Departments. 

SELECT   department_name, city   FROM   departments   NATURAL JOIN   locations;

In this query we are selecting department name from departments table and city from locations table. You can select other columns too.

Departments is our source table as it’s coming right after FROM keyword and locations is our Target table as it’s coming after JOIN clause. 

The best part of using Natural join is that you do not need to specify the join column because column with the same name in source and target tables are automatically associated with each other. 

On executing this query oracle returns all the rows from department column of departments table and from city column of locations table, where location id of departments table is equal to the location id of location table. 

Scenario 2: What if when our source and target tables have more than one identical name column.


To demonstrate this scenario we will use employees and departments table. These two tables share two common columns which are the department id and the manager id.

Here if we put Natural join on these two tables then oracle engine will use these two common columns to return the result.

Say we want to see the name of an employee and the name of the department in which he or she works. For that we will first select a name from the employee table which is our source table and the department’s name from departments table.

SELECT   first_name, department_name   FROM   employees   NATURAL JOIN   departments;

As I have already mentioned that when we use natural join, there is no need to specify join columns explicitly. All the columns which are common in both the source and the target table get associated automatically.

The queries in which all the common columns of source and target table get associated automatically are known as Pure Natural Join.

This means that in this particular query, oracle has automatically associated our common columns which are department id and manager id respectively.

To help you understand more clearly let’s write the same query but this time using ON clause.

SELECT   first_name,department_name   FROM   employees  JOIN    departments   ON   (employees.manager_id = departments.manager_id   AND   employees.department_id = departments. department_id);

Here is a slight change in the natural join syntax as we are using ON clause this time. So instead of writing natural join we will only write JOIN.

Both these queries produce the same results as both are the same queries just written in a different way.

In this query we emphasize natural join using ON clause. As we are using ON clause thus it becomes mandatory to specify the columns over which we want to perform the join.

Scenario 3: Natural join with USING clause

Here Once again we will use our natural join query.

SELECT   first_name, department_name   FROM   employees    NATURAL JOIN   departments;

As we can see here in this query that when we have more than one common column then the oracle engine uses all of them to produce the result.

Now, here in this query we are using employees and department tables which have 2 common columns department id and manager id. 

What if you want to use only department id in your natural join instead of both manager id and department id? 

In this situation we use natural join with USING clause. 

Say we want to select all the first names and department names from the source table employees and target table departments from where we have equal values of manager ids in both the employees table and the departments table. 

Let’s write the query

SELECT   first_name,department_name   FROM   employees   JOIN   departments   USING(manager_id);

That’s how you do it. I hope you enjoyed reading.

Also do help me by sharing this article with your friends and on your social media. And subscribe to my YouTube channel for more free Oracle/SQL tutorials.

Thanks & have a great day!