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
A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
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.
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 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
I hope you enjoyed reading.
Help me by sharing this article with your friends and on your social media.
And subscribe to my YouTube channel for Oracle/SQL tutorials.
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
SQL Script used in Video and in this article
Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE