Natural Joins.

Definition:

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
Source: Oracle

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

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

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

When the join query is executed then oracle starts matching 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. 

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 the foreign key and thus has 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 its coming right after FROM keyword and locations is our Target table as its 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 table 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 shares two common columns which are department id and 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 employee and the name of department in which he or she is working. For that we will select first name from employee table which is our source table and department 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 source and 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.

Which means in this query, oracle has automatically associated our common columns which are department id and manager id.

To help you understand more clearly let’s write 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, instead of writing natural join we will only write JOIN.

Both these queries produce the same results as both are same query written in different ways.

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 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 and department id? 

In this situation we use natural join with USING clause. 

Say we want to select all the first name and department name from 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);

I hope you enjoyed reading.