What is a natural join in SQL?
The join clause is used to combine tables based on a common column and a join condition. A natural join is a type of join that combines tables based on columns with the same name and type.
Remember:
- There is no need to specify the column names to join.
- The resultant table does not contain repeated columns.
- It is possible to perform a natural join on more than two tables.
Syntax
SELECT * FROMTableName1 NATURAL JOIN TableName2;
After the SELECT keyword, the names of the columns that are to be included in the result of the query are specified. The * operator is used if all the columns need to be selected.
After the FROM keyword, the tables which need to be joined are specified, and the NATURAl JOIN keyword is written between the table names.
Example
/*Creating the tables*/CREATE TABLE product_list(ID varchar(20),Pname varchar(20));CREATE TABLE product_details(ID varchar(20),Brand varchar(20),Origin varchar(20));CREATE TABLE brand_details(Brand varchar(20),OfficeAddress varchar(20));/*Inserting values into product_list*/INSERT INTO product_list(ID, Pname)VALUES("1", "Apples");INSERT INTO product_list(ID, Pname)VALUES("2", "Oranges");INSERT INTO product_list(ID, Pname)VALUES("3", "Mangoes");/*Inserting values into product_details*/INSERT INTO product_details(ID, Brand, Origin)VALUES("1", "Fresh Foods", "USA");INSERT INTO product_details(ID, Brand, Origin)VALUES("2", "Angro Ltd", "Pakistan");/*Inserting values into brand_details*/INSERT INTO brand_details(Brand, OfficeAddress)VALUES("Fresh Foods", "123 Seattle USA");INSERT INTO brand_details(Brand, OfficeAddress)VALUES("Angro Ltd", "124 Lahore");/*Performing the natural join with two tables*/SELECT * FROMproduct_list NATURAL JOIN product_details;/*Performing the natural join with three tables*/SELECT * FROMproduct_list NATURAL JOIN product_detailsNATURAL JOIN brand_details;
Unlock your potential: SQL joins series, all in one place!
To continue your exploration of SQL joins, check out our series of Answers below:
Different types of SQL joins
Understand the different types of SQL joins used for combining data from multiple tables.What is inner join in SQL?
Learn how inner joins return matched rows from both tables in SQL.What is outer join in SQL?
Explore outer joins and how they return unmatched rows as well.What is a self join in SQL?
Discover how self joins allow a table to join with itself.What is a natural join in SQL?
Learn how natural joins match columns with the same name in SQL tables.What is a hash join in SQL?
Understand hash joins, which are used for large-scale data matching and combining.What is the SQL CROSS JOIN?
Explore the SQL CROSS JOIN to combine every row from one table with every row from another.What is an equi join in SQL?
Learn how equi joins match rows based on the equality of values between tables.How to join 3 or more tables in SQL
Master the technique of joining multiple tables in a SQL query for complex data retrieval.
Free Resources