SQL Natural Join

A Natural Join is a SQL join operation that merges rows from two tables based on the columns with the same name and data type in both tables. The key feature of a natural join is that it automatically finds the common columns between the tables and uses them to perform the join.

What is SQL Natural Join?

A Natural Join performs a join between two tables based on columns that have the same name and compatible data types. This join operation automatically determines the common columns between the tables and removes duplicates in the result set. The resulting table will contain only one copy of the common columns, making the result concise and free from redundant data.

Syntax:

SELECT *

FROM TABLE1

NATURAL JOIN TABLE2;

Features of Natural Join

• Cartesian Product: Like other join operations, a natural join essentially combines every row from one table with all rows from another table, but with additional conditions to match common attributes.

• Eliminates Duplicate Columns: If two tables share a column (or columns) with the same name, the natural join will keep only one copy of the shared column(s), eliminating redundancy.

• Automatic Column Matching: Natural joins automatically identify and match columns with the same name and compatible data types. This eliminates the need to manually specify the columns in the ON clause as with an INNER JOIN.

Steps to implement SQL Natural Join 

Let’s break down the process of performing a Natural Join through a practical example. We will create two tables and join them using SQL Natural Join.

Step 1: Creating Database 

First create a database named geeks:

create database geeks;

Step 2: Use the Database

Now, use the geeks database to create the tables:

use geeks;

Step 3: Create Tables

Let’s define two tables in the geeks database: department and employee.

1. Department Table 

Create Table department (
DEPT_NAME Varchar(20),
MANAGER_NAME Varchar(255)
);

2. Employee Table 

Create Table employee (
EMP_ID int,
EMP_NAME Varchar(20),
DEPT_NAME Varchar(255)
);

Step 4: Insert Data into the Tables

Let’s insert sample data into both the department and employee tables.

1. Insert data into the department table:

INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ( "IT", "ROHAN");
INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ( "SALES", "RAHUL");
INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ( "HR", "TANMAY");
INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ( "FINANCE", "ASHISH");
INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ("MARKETING", "SAMAY");

2. Insert data into the employee table.

INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (1, "SUMIT", "HR");
INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (2, "JOEL", "IT");
INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (3, "BISWA", "MARKETING");
INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (4, "VAIBHAV", "IT");
INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (5, "SAGAR", "SALES");

Step 5: Verify the Inserted Data

To ensure the data has been inserted correctly, run the following queries to view the data in both tables:

SELECT * FROM employee;

Output :

Query:

SELECT * FROM department;

Output :

Step 6: Perform SQL Natural Join

Now, we can perform the Natural Join between the employee and department tables. The join will be based on the DEPT_NAME column, which is common to both tables.

Query:

SELECT *
FROM employee
NATURAL JOIN department;

Output :

Explanation: As shown, the DEPT_NAME column appears only once in the result, even though it is present in both tables. The other columns from both tables are included, and we now have a joined table that combines employee details with department information.

Difference Between  Natural Join and Inner Join 

While Natural Join and Inner Join are both used to combine data from two tables, they differ in the following ways:

1. Natural Join:

• Automatically joins tables based on common columns (with the same name and data type).
• Only one copy of each common column is included in the result.
• No need to specify the joining condition explicitly.

2. Inner Join:

• Requires you to explicitly specify the columns to join using the ON clause.
• Includes all common columns from both tables in the result, which may lead to duplication of column names.
• Provides more control over the join condition.