SQL Natural Join

In this article, we will discuss the overview of SQL Natural Join and then mainly focus to implement query with the help of examples. Let’s discuss it one by one.

Overview :
Natural join is an SQL join operation that creates 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.

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 :

create database geeks;

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

use geeks;

Step-3: Reference tables into the database :
This is our tables in the geeks database as follows.

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 into the tables as follows.

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

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.

SELECT * FROM EMPLOYEE;

Output :

EMP_ID	EMP_NAME	DEPT_NAME
1	SUMIT	HR
2	JOEL	IT
3	BISWA	MARKETING
4	VAIBHAV	IT
5	SAGAR	SALES

SELECT * FROM DEPARTMENT;

Output :

DEPT_NAME	MANAGER_NAME
IT	ROHAN
SALES	RAHUL
HR	TANMAY
FINANCE	ASHISH
MARKETING	SAMAY

Step-6: Query to implement SQL Natural Join :

SELECT *
FROM EMPLOYEE
NATURAL JOIN DEPARTMENT;

Output :

EMP_ID	EMP_NAME	DEPT_NAME	MANAGER_NAME
1	SUMIT	HR	TANMAY
2	JOEL	IT	ROHAN
3	BISWA	MARKETING	SAMAY
4	VAIBHAV	IT	ROHAN
5	SAGAR	SALES	RAHUL

My Personal Notes arrow_drop_up

Save