Open In App

SQL Natural Join

Improve
Improve
Like Article
Like
Save
Share
Report

Natural join is an SQL join operation that creates a join on the base of the common columns in the tables. To perform natural join there must be one common attribute(Column) between two tables. Natural join will retrieve from multiple relations. It works in three steps.

In this article, we will discuss the overview of SQL Natural Join and then mainly focus to implement queries with the help of examples. 

Syntax :
We will perform the natural join query by using the following syntax.

SELECT *

FROM TABLE1

NATURAL JOIN TABLE2;

Features of Natural Join

Here, we will discuss the features of natural join.

  1. It will perform the Cartesian product.
  2. It finds consistent tuples and deletes inconsistent tuples.
  3. Then it deletes the duplicate attributes.

Steps to implement SQL Natural Join 

Here, we will discuss the steps to implement SQL Natural Join as follows.

Step 1:Creating Database 

Query:

create database geeks;

Step 2: Using the Database. To use this database as follows.

Query:

use geeks;

Step 3: Reference Tables Into The Database .These are our tables in the geek’s database as follows.

Query:

Table-1: Department 

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

Table-2: Employee 

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

Step 4: Inserting values. Add value to the tables as follows.

Query:

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");

Now, insert data in the employee table.

Query:

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: Verifying Inserted Data.This is our data inside the table as follows.

Query:

SELECT * FROM employee;

Output :

 

Query:

SELECT * FROM department;

Output :

 

Step 6: Query to Implement SQL Natural Join.

SELECT *
FROM employee
NATURAL JOIN department;

Output :

 

Difference between  Natural Join and Inner Join 

Natural Join joins two tables based on the same attribute name and datatypes. The resulting table will contain all the attributes of both the table but keep only one copy of each common column while Inner Join joins two tables on the basis of the column which is explicitly specified in the ON clause. The resulting table will contain all the attributes from both tables including the common column also.


Last Updated : 03 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads