EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Natural Join in Oracle

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Oracle Tutorial » Natural Join in Oracle

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;

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,901 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle - 250+ Courses, 40+ Projects

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

Let’s Get Started

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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

EDUCBA

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

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

Special Offer - Oracle Course Training Learn More