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 :
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.
Similar Reads
PL/SQL JOIN JOIN is a powerful operation in PL/SQL that allows us to combine data from two or more related tables based on a common key. The PL/SQL JOIN is used to select data from multiple tables using this key to match records. This powerful PL/SQL feature allows for selecting data across multiple tables usin
5 min read
SQL Outer Join SQL Outer Joins allow retrieval of rows from two or more tables based on a related column. Unlike inner Joins, they also include rows that do not have a corresponding match in one or both of the tables. This capability makes Outer Joins extremely useful for comprehensive data analysis and reporting,
4 min read
PL/SQL Outer Join In SQL, joins are used to retrieve data from two or more tables based on a related column. Joins can be categorized into different types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, etc. In this article, we will learn about OUTER JOIN in PL/SQL, including its types, syntax, use cases, and ex
5 min read
SQL LEFT JOIN In SQL, LEFT JOIN retrieves all records from the left table and only the matching records from the right table. When there is no matching record found, NULL values are returned for columns from the right table. This makes LEFT JOIN extremely useful for queries where you need to retain all records fr
5 min read
PL/SQL Left Join In the world of database management, efficiently retrieving and combining data from multiple tables is crucial. Among these the LEFT JOIN is particularly important because it includes all records from one table even when there are no corresponding records in another table. In this article, we will P
6 min read