Natural join SQL | What is Natural join in SQL and How to use it?
In this article, you will learn NATURAL JOIN SQL to query data from two or more tables.
A natural JOIN SQL is a join that creates an implicit join which based on the same column in the joined tables. furthermore, the join clause used for combine tables based on a common column and a join condition.
Therefore, we can say that a natural join SQL is a type of join that combines tables, it is based on columns with the same name and type or
the natural join is a type of EQUI JOIN and it is structured in such a way that, columns with the same name of associated tables will appear once only.
The resultant table does not contain repeated columns.
It is possible to perform a natural join on more than two tables.
NATURAL JOIN SQL Syntax
|
1 2 |
SELECT * FROM TableName1 NATURAL JOIN TableName2; |
SELECT keyword, the names of the columns, which are to be included in the results of the query are specified. so, the * operator is used, when 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 the keyword is written between the table names.
The columns should be the same (constant)data type.
Don’t use the ON clause in an exceedingly natural join SQL.
Representation of Natural JOIN SQL
SQL NATURAL JOIN is the same as EQUI JOIN but different is resulting contains allow only one column for each pair of same columns named. Recordset contains haven’t same name columns are found.
Example:
let’s see the NATURAL JOIN SQL example between two tables: the first table goods and the second table is company
Sample table: goods
| Item_ID | Item_Name | Item_Unit | Company_ID |
|---|---|---|---|
| 1 | Bakery goods | Pcs | 10 |
| 4 | Whole-wheat bread | Pcs | 12 |
| 6 | Chocolate cookie | Pcs | 13 |
| 7 | Olive oil | Pcs | 15 |
| 9 | sunflower seed oil | Pcs | 18 |
| 10 | Corn oil | Pcs | 19 |
| 12 | Chocolate candies | Pcs | 20 |
| 14 | Mineral water | Pcs | 20 |
| 16 | corn flakes | Pcs | 20 |
| 17 | Basmati Rice | Pcs | 24 |
| 18 | Biscuits and toast | Pcs | 25 |
| 20 | Spaghetti | Pcs | 27 |
| 21 | Cranberry jam | Pcs | 29 |
Sample table: company
| Company_ID | Company_Name | Company_City |
|---|---|---|
| 12 | Patanjali | Indore |
| 18 | Mastered | Kanpur |
| 20 | Neslef | Jhansi |
| 24 | Patanjali | Allahabad |
| 29 | Burberry | Delhi |
| 13 | cafe coffee | Jaipur |
| 25 | Inderbiscut | Lucknow |
| 27 | Patanjali | Dehradun |
furthermore, to get the all unique columns from goods and company tables, there are following SQL statement can be used:
SQL CODE
|
1 2 3 |
SELECT * FROM goods NATURAL JOIN company; |
| Company_ID | Item_ID | Item_Name | Item_unit | Company_Name |
|---|---|---|---|---|
| 12 | 4 | Whole-wheat bread | Pcs | Patanjali |
| 18 | 9 | sunflower seed oil | Pcs | Mastered |
| 20 | 12 | Chocolate candies | Pcs | Neslef |
| 20 | 14 | Mineral water | Pcs | Neslef |
| 20 | 16 | corn flakes | Pcs | Neslef |
| 24 | 17 | Basmati Rice | Pcs | Patanjali |
| 29 | 21 | Cranberry jam | Pcs | Burberry |
| 13 | 6 | Chocolate cookie | Pcs | cafe coffee |
| 25 | 18 | Biscuits and toast | Pcs | Inderbiscut |
| 27 | 20 | Spaghetti | Pcs | Patanjali |
NATURAL JOIN SQL examples
Therefore, to demonstrate the Natural JOIN SQL, first, we create the two tables such as department and goodsCREATE TABLE statements create the department and goods table.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE department ( department_id serial PRIMARY KEY, department_name VARCHAR (255) NOT NULL ); CREATE TABLE goods ( goods_id serial PRIMARY KEY, goods_name VARCHAR (255) NOT NULL, goods_id INT NOT NULL, FOREIGN KEY (goods_id) REFERENCES department (department_id) ); |
furthermore, every department has many goods or zero, whereas each good belongs to one and one department. the department_id column in the goods table is the foreign key that references the primary key of the department table. Therefore, the department_id is the common column that we use to perform the NATURAL JOIN SQL.
therefore, there is the following INSERT statement insert some sample data into the department and goods tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
INSERT INTO department (department_name) VALUES ('AC'), ('Cooler'), ('TV'); INSERT INTO goods (goods_name, department_id) VALUES ('Hitachi', 2), ('Remastered', 2), ('Bajaj', 1), ('Usha', 1), ('Intex', 3), ('LG', 3); |
so, the following statement used for the NATURAL JOIN clause to join the goods table with the department table.
SQL CODE:
|
1 2 3 |
SELECT * FROM goods NATURAL JOIN department; |
| department_id | goods_id | goods_name | department_name |
|---|---|---|---|
| 1 | 1 | Hitachi | AC |
| 1 | 2 | Remastered | AC |
| 2 | 3 | Bajaj | Cooler |
| 2 | 4 | Usha | Cooler |
| 3 | 5 | Intex | TV |
| 3 | 6 | LG | TV |
Therefore, the above statement is equivalent to the following statement that uses the INNER JOIN clause.
|
1 2 3 |
SELECT * FROM goods INNER JOIN department USING (department_id); |
therefore, the convenience of the NATURAL JOIN is that it does need you then specify the join clause because it uses an implicit join clause which is based on the common column.
so, however, you need to avoid using the NATURAL JOIN whenever possible because sometimes it may cause an unexpected result.
let’s suppose, for example, take a look at the city and country tables. Both tables have the same country_id column so, we can use the NATURAL JOIN to join these tables as follows:
|
1 2 3 |
SELECT * FROM city NATURAL JOIN country; |
country_id | last_update | city_id | city | country
————+————-+———+——+———
(0 rows)
Therefore, the query returns an empty result/output set.
Imporatnt Note: The reason is that both tables also have a commonly named last_upadte, which can not be used for the join. furthermore, however, the NATURAL JOIN clause just uses the last_update column.
About Author
