EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials SQL Tutorial Natural Join SQL

Natural Join SQL

Roja Metla
Article byRoja Metla
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated March 27, 2023

Natural Join SQL

Introduction to Natural Join SQL

Natural join SQL is a join that is similar to the Equi join. The main difference is that the number of columns that are returned in the result set.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The things that need to be taken care in the Natural Join is as below:

  • We don’t use the ON clause in Natural Join.
  • The tables which we are using for the Natural join should have one or more identical column name.
  • The identical column name should be of the same data type.

The main difference between the INNER join and the Natural join is the duplicate column set in the result. Let us discuss in details below.

Syntax:

Select *
From tablename1
NATURAL JOIN tablename2; - - - - - - - - - - - as mentioned no 'ON' condition - - - - -

How to Implement Natural join in SQL?

Let us consider another two tables below and implement the Natural join between them. Below are two tables named “Trip” table and “Package” table.

Example

Here once we apply the Natural join we get the common rows between two tables.

Trip Table:
Natural join sql - Trip TablePackage Table:

Natural join sql - Package Table

Query to get natural join between the above two tables:

Code:

SELECT *
FROM TRIP
NATURAL JOIN PACKAGE;

Output:

Between two tables

Let us fetch only a few columns from the above.

Query to get natural join between the above two tables:

Code:

SELECT trip_no,place_name, max_people, package_no, amount, days
FROM TRIP
NATURAL JOIN PACKAGE;

Let’s check the output of the above table after applying the Left join on them.

Output:

Applying the Left join

Characteristics of Natural Join and Inner Join

There is one notable characteristic between Natural Join and Inner Join is the number of columns returned.

1. INNER Join

All common rows from both tables. While joining at least one column should be of the same data type and common among tables.

Example: Let us consider the loan table and borrower table to apply the inner join.

Let us build a query to get the loan_no, status and borrower date from two tables: –

Code:

SELECT L.LOAN_NO, L.LOAN_STATUS, B.BORROWER_DATE
FROM LOAN L INNER JOIN BORROWER B
ON L.LOAN_NO=B.LOAN_NO

The above table after applying the inner join on them.

Output:

 Natural join sql - Query3

Here in the above output, we got the common rows of both tables based on the condition “L.LOAN_NO=B.LOAN_NO”.

2. Natural Join

Natural join is a join that combines two or more common columns between two tables.

Example: Let us consider two tables and apply Natural join on the tables

Below are the two tables, Loan Table &

Loan Table:

Loan Table

Borrower Table:

Borrower Table

Let us build a query to get the loan_no, status and borrower date from two tables:

Code: 

SELECT *
FROM LOAN
NATURAL JOIN BORROWER;

The above table after applying the natural join on them.

Output: 

Natural join sql - query4

Conclusion

To fetch data relevant to the customer requirement we might need to join tables which will be fulfilled by joins. As mentioned earlier joins are used to get data from more than one table. To join more than one table we need at least one column common in both tables. Tables get joined based on the condition specified.

Recommended Articles

We hope that this EDUCBA information on “Natural Join SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Right Join
  2. SQL Clauses
  3. NOT in MySQL
  4. LIKE in MySQL
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

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

Let’s Get Started

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

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

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?

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

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW