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.

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:

Package Table:

Query to get natural join between the above two tables:

Code:

SELECT *
FROM TRIP
NATURAL JOIN PACKAGE;

Output:

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:

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:

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:

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: 

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

This is a guide to Natural Join in SQL. Here we discuss the introduction to Natural join SQL, the characteristics of Natural Join and Inner Join along with respective examples. You can also go through our other suggested articles to learn more –

1. SQL Right Join
2. SQL Clauses
3. NOT in MySQL
4. LIKE in MySQL