EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Natural Join in Oracle

Natural Join in Oracle

By Priya PedamkarPriya Pedamkar

Natural Join in Oracle

Introduction to 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.

Points of Concentration:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • A Natural Join combines multi Tables, View or Materialized View to retrieve data.
  • A Natural Join returns all rows that satisfy the same column name and data type condition.
  • Oracle provides the Natural Join keyword to perform Natural Join.
  • If the Natural Join doesn’t satisfy the same column name and data type condition then it returns a Cartesian result.
  • Natural Join does not exclude duplicate record(s).

Syntax:

SQL> SELECT Column_1, Column_2, Column_n FROM Table1 NATURAL JOIN                            Table2 WHERE condition;

Description:

  • Col_1/2/n: The column(s) or calculation as per your requirement.
  • Table 1/2: As per table participation in Natural Join.
  • WHERE: It’s an optional. It is used to filter the output result.

Example:

SQL> SELECT Name, Designation, Salary, State, Deptnumber FROM Employee
NATURAL JOIN Dept_Category;

Output:

natural join in oracle 1

Rules and Restrictions of Natural Join in Oracle

Given below are rules and regulations:

  • 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.
  • 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.
  • The column(s) is/are used in Natural Join can’t have a column qualifier.

Examples of Natural Join in Oracle

Given below are the examples. Here we will use the below sample table (Employee & Dept_category) with 14 & 8 records.

SQL> SELECT * from Employee;

Output:

natural join in oracle 2

SQL> SELECT * from Dept_Category;

Output:

natural join in oracle 3

Example #1

Natural Join with single common column and without any condition.

SQL > SELECT Name, Designation, Salary, State, Deptnumber FROM Employee
NATURAL JOIN Dept_Category;

Output:

natural join in oracle 4

In the above example, Natural Join combines tables (Employee and Dept_Category) using the Deptnumber column because of only this column full filling the same column name and data type condition in both the tables.

Example #2

Natural Join with multiple common column and without any condition.

SQL> SELECT Designation, Sal, DOJ, Deptnumber FROM Emp NATURAL JOIN Employee;

Output:

with multiple common column

In the above example, Emp and Employee tables participating for Natural Join and both the tables have two common columns (DOJ & DEPTNUMBER) and each table has 14 records and Deptnumber column’s all data matches with each other table. But output returns only 13 records. WHY?

Because in the above example there are two common columns (DOJ & DEPTNUMBER) participating as a join condition. So Deptnumber column’s data getting matched with each other table but from Emp table DOJ column has one unmatched record with other table so Natural Join exclude that record and returns 13 records.

Example #3

Natural Join without any common column.

SQL> SELECT Designation, Sal, DOJ, Deptnumber FROM Emp NATURAL JOIN Employee;

Output:

This JOIN statement returns 196 records.

But each table consists 14 records only. WHY 196 records?

Because there is no common column or same column name and data type in both the tables. Natural Join does not find any common column name and data type, so it performs join without join condition means it joins every single record from one table with every single record from another table (14* 14). That’s why it returns 196 records. This kind of operation or result set is known as a CARTESIAN product.

Example #4

Oracle Natural Join with WHERE clause.

SQL > SELECT Name, Designation, Salary, State, Deptnumber FROM Employee
NATURAL JOIN Dept_Category WHERE Deptnumber =10;

Output:

WHERE clause

In the above example, WHERE clause condition filters the result and returns only those records which are having Deptnumber is 10.

Example #5

Natural Join with Column Qualifier.

SQL > SELECT E.Name, E.Designation, E.Salary, D.State, D.Deptnumber
FROM Employee E NATURAL JOIN Dept_Category D WHERE Deptnumber =10;

Output:

Column Qualifier

In the above example, E and D is the alias name of tables respectively. The alias names are being used by columns as a qualifier to retrieve the data from that specified tables but Natural Join does not accept column qualifier and throws an error. So column qualifier can’t be a part for Natural Join operation.

Tips:

  • An asterisk (*) can be used to retrieve all fields of the tables that are participating in join. But it join columns left table columns left side and right table columns right side.
  • Oracle Natural Join is similar by nature to Oracle proprietary EQUI JOIN with join condition.

Conclusion

Oracle Natural Join is a join query which 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 declared.

Recommended Articles

This is a guide to Natural Join in Oracle. Here we discuss the introduction, rules, and restrictions of Natural Join in Oracle with respective examples. You may also have a look at the following articles to learn more –

  1. Relational Database Model
  2. BETWEEN in Oracle
  3. GROUP BY in Oracle
  4. Oracle Procedures
GOLANG Certification Course
29+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
iOS DEVELOPER Certification Course
196+ Hours of HD Videos
61 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JAVA SERVLET Certification Course
70+ Hours of HD Videos
18 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
RED HAT LINUX Certification Course
40+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
ORACLE DBA Database Management System Course
 28+ Hours of HD Videos
2 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more