Skip to main content
Home
  • Tutorials
    • Quality Assurance
    • Software Development
    • Machine Learning
    • Data Science
  • About Us
  • Contact
programsbuzz facebook programsbuzz twitter programsbuzz linkedin
  • Log in

Main navigation

  • Tutorials
    • Quality Assurance
    • Software Development
    • Machine Learning
    • Data Science
  • About Us
  • Contact

Oracle Natural Join

Profile picture for user akshita.goel
Written by akshita.goel on 10/25/2021 - 10:38

NATURAL JOIN in Oracle is the type of Joins that can easily access two or more tables at a similar time with the common column present in those tables.

Natural Join Syntax in Oracle

SELECT Column from table1 NATURAL JOIN table2;

Remember

  1. The column of the same name must have the same datatype as well.
  2. It does not exclude duplicate rows.
  3. In case Natural join does not satisfy data type and same column name condition then it will return the cartesian result.

Oracle Natural Join Example 

Suppose we have two tables Stu and details as below:

Stu:

natural join oracle

details:

natural join sql oracle

Now, if we want to combine two of the table by a common column Roll_No:

Select * from Stu s, details d
where s.Roll_No = d.Roll_No;

Output

what is natural join in oracle

The same can be done using NATURAL JOIN. However, making the syntax much easier.

Now, if we want to perform the same operation using NATURAL JOIN, the code will be as follows:

Select * from Stu NATURAL JOIN details;

Output

oracle natural join

NOTE The only difference you can find here is that the duplicate columns are not allotted to the common column as it was done in the very first example, without using Natural Join.

How many total rows will be there in Output of Natural Join?

oracle natural join

Example

We have the following Stu Table:

oracle sql natural join

And below is the details table:

example of natural join in oracle

If we want to apply Natural Join to both the Stu and details table, the Output table will contain the following rows:

Unique values= 180 and 17 i.e. 1+1 = 2 Rows

Duplicate Values= 16, 16 and 16 i.e. 3 Duplicate values. So, 3 * 3 = 9 Rows

Total output Rows= 2 + 9 = 11 Rows.

Output

oins sql natural oracle

Related Content
Oracle Tutorial
Oracle: Cross Join
Oracle SQL Set Operators
  • Log in or register to post comments

Choose Your Technology

  1. Agile
  2. Apache Groovy
  3. Apache Hadoop
  4. Apache HBase
  5. Apache Spark
  6. Appium
  7. AutoIt
  8. AWS
  9. Behat
  10. Cucumber Java
  11. Cypress
  12. DBMS
  13. Drupal
  14. GitHub
  15. GitLab
  16. GoLang
  17. Gradle
  18. HTML
  19. ISTQB Foundation
  20. Java
  21. JavaScript
  22. JMeter
  23. JUnit
  24. Karate
  25. Kotlin
  26. LoadRunner
  27. matplotlib
  28. MongoDB
  29. MS SQL Server
  30. MySQL
  31. Nightwatch JS
  32. PactumJS
  33. PHP
  34. Playwright
  35. Playwright Java
  36. Playwright Python
  37. Postman
  38. Project Management
  39. Protractor
  40. PyDev
  41. Python
  42. Python NumPy
  43. Python Pandas
  44. Python Seaborn
  45. R Language
  46. REST Assured
  47. Ruby
  48. Selenide
© Copyright By iVagus Services Pvt. Ltd. 2023. All Rights Reserved.

Footer

  • Cookie Policy
  • Privacy Policy
  • Terms of Use