NATURAL JOIN in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

NATURAL JOIN in Oracle with Examples

In this article, I am going to discuss the NATURAL Join in Oracle with Examples. Please read our previous article where we discussed SELF Join in Oracle with Examples.

What is NATURAL JOIN in Oracle?

A Natural Join in Oracle is a SQL query technique that combines row(s) from two or more Tables, View or Materialized View. A Natural Join performs join based on column(s) of the tables which are participating in a join that have the same column name and data type. To perform this join operation, the Natural Join keyword explicitly is used.

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 an INNER join.

If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):

1. All the common columns
2. Every column in the first (left) table that is not a common column
3. Every column in the second (right) table that is not a common column
4. An asterisk qualified by a table name (for example, COUNTRIES*) will be expanded to every column of that table that is not a common column.

If a common column is referenced without being qualified by a table name, the column reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column point to the column in the second (right) table.

Rules and Restrictions of Natural Join in Oracle

Given below are rules and regulations:

1. The Natural Join does not require to pass join condition explicitly (common column condition), it finds automatically common column name and data type and performs join.
2. If all participating tables, more than one column have the same name and data type then Natural Join would use them as well for joining conditions.
3. The column(s) is/are used in Natural Join can’t have a column qualifier.

Syntax:

SELECT table1.column, table2.column
FROM table1
NATURAL JOIN table2;

Where table1, table2 are the name of the tables participating in joining

Points to Remember:

1. The join is based on all the columns in the two tables that have the same name and data types.
2. The join creates, by using the NATURAL JOIN keywords.
3. It selects rows from the two tables that have equal values in all matched columns.
4. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

Example: Oracle Natural Joins

We are going to use the following Department and Employee tables to understand Natural JOINs in Oracle.

Please use the below SQL script to create and populate the Department and Employee tables with the required sample data.

DROP Table Department;
CREATE TABLE Department (
  DepartmentId INT,
  DepartmentName VARCHAR(15)
);

INSERT INTO Department (DepartmentId, DepartmentName) VALUES (10, 'IT');
INSERT INTO Department (DepartmentId, DepartmentName) VALUES (20, 'HR');
INSERT INTO Department (DepartmentId, DepartmentName) VALUES (30, 'Finance');

DROP Table Employee;
CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  DepartmentId INT,
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Age INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1001, 'John', 10, 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1002, 'Smith', 20, 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1003, 'James', 30, 50000, 'Male', 28, 'Delhi');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1004, 'Mike', 30, 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1005, 'Linda', 20, 75000, 'Female', 26, 'Mumbai');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 10, 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 20, 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 10, 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 10, 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1010, 'Hina', 20, 75000, 'Female', 26, 'Mumbai');

Oracle Natural Join Example:

In the below example, the Employee table is joined to the Department table by the DepartmentId column, which is the only column of the same name in both the tables. If other common columns were present, the join would have used them all.

SELECT Name, Salary, Gender, Age, City, DepartmentName
FROM Employee
NATURAL JOIN Department;

When you execute the above code, you will get the following output.

Natural Joins with a WHERE Clause

You can also implement additional restrictions on a natural join using a WHERE clause. In the previous example, the Employee table was joined to the DEPARTMENT table by the DepartmentId column, now you can limit the rows of output to those with only gender equals Male as shown in the below query.

SELECT Name, Salary, Gender, Age, City, DepartmentName
FROM Employee
NATURAL JOIN Department
WHERE Gender = 'Male';

When you execute the above code, you will get the following output.

Note: Oracle Natural Join is a join query that combines multiple Tables, View, or Materialized View to retrieve data. It does not need to declare the join condition explicitly because it considers implicitly a join condition based on the same column name and data type. So, it can be used to get the records for single or multiple join conditions without explicitly declaring.

Difference between Natural JOIN and INNER JOIN in Oracle:

NATURAL JOIN:

1. Natural Join joins two tables based on the same attribute name and datatypes.
2. In Natural Join, the resulting table will contain all the attributes of both the tables but keep only one copy of each common column.
3. In Natural Join, if there is no condition specified then it returns the rows based on the common column.
4. Syntax: SELECT * FROM table1 NATURAL JOIN table2;

INNER JOIN:

1. Inner Join joins two tables on the basis of the column which is explicitly specified in the ON clause.
2. In Inner Join, the resulting table will contain all the attributes of both the tables including duplicate columns also.
3. In Inner Join, only those records will return which exists in both the tables.
4. Syntax: SELECT * FROM table1 INNER JOIN table2 ON table1.Column_Name = table2.Column_Name;

In the next article, I am going to discuss EQUI JOIN in Oracle with Examples. Here, in this article, I try to explain the NATURAL Join in Oracle with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.