In this section, we are going to understand the working of PostgreSQL Natural join, which is used to join two or more than two tables.
What is the PostgreSQL Natural Join clause?
The natural join is where multiple tables are combined, and as an output, we will get the new rows, which is intended to join the columns for each of the tables. And it is also used to combine the tables, which creates an implicit join depend on similar column names in the combined tables.
In other words, we can say that the PostgreSQL Natural Join clause essentially creates a temporary table for a set of rows to work on several (two or more) tables.
And those tables will be specified in the join condition, and at least has one mutual column, and these standard columns should have a relation between them.
By default, PostgreSQL will use the INNER JOIN operation. And it can be used with LEFT JOIN, INNER JOIN or RIGHT JOIN, but the type of join must be defined in the joining or PostgreSQL will use the INNER JOIN operation by default.
Syntax of PostgreSQL Natural Join
The Natural Join keyword is used with the SELECT command and must be written after the FROM Keyword.
SELECT [Column_list |*]
FROM Table1
NATURAL [INNER, LEFT, RIGHT] JOIN Table2;Note: In the above syntax, we can also use an asterisk (*) in place of Column-list as the asterisk will generate the output that contain the below fields:
- When both tables have a unique column, which contains different column names.
- When a table has a common field and where both columns have a same name.
Example of PostgreSQL Natural join
Let us see an example to understand how the PostgreSQL Natural join works:
To join two tables by using PostgreSQL Natural Join
For this, we will create two tables named Course_categories and Course tables with the help of the CREATE command and insert some values using the INSERT command.
Firstly, we are going to create Course_categories and Course tables by using the CREATE command:
The following statement is used to create the Course_categories table:
SELECT [Column_list |*]
FROM Table1
NATURAL [INNER, LEFT, RIGHT] JOIN Table2;The below command is used to create the Course table:
CREATE TABLE Course (
Course_id serial PRIMARY KEY,
Course_name VARCHAR NOT NULL
);The Course_categories and Course tables have been successfully created after executing the above commands.
In the above tables, all the Course categories have zero or several courses, but here all the courses are linked to unique Course categories.
In the Course_catagories table, the Cousre_id column is the Foreign key, which is referred to as the Primary key of the Course table.
We will use to perform the PostgreSQL Natural Join as the Course_id is the standard column in both tables.
Once both the tables have been generated, we are ready to insert some values into it by using the INSERT command as follows:
In the below command, we are inserting the values in the Course_catagories table:
INSERT INTO Course_categories (Course_category, Course_id)
VALUES
('Adobe Photoshop', 1),
('Adobe Illustrator', 1),
('JavaScript', 2),
('Advance CSS', 2),
('Machine Learning', 2),
('AWS', 3),
('CCNA', 3),
('Kubernetes', 3),
('Social Media Marketing', 4),
('Digital Marketing', 4);In the below command, we are inserting the values in the Course table:
INSERT INTO Course (Course_name)
VALUES
('Design'),
('Development'),
('IT & Software')
('Marketing');After creating and inserting the values in the Course_categories and Course table, we will use the SELECT command to see existing records on the particular tables:
Select * from Course_categories; Output
After implementing the above command, we will get the following data from the Course_categories table:
Table2: Course
Select * from Course; Output
After executing the above command, we will get the following records from the Course table:
The below query uses PostgreSQL Natural Join clause to combine records from Course and Course_categories tables.
SELECT *
FROM Course_categories
NATURAL JOIN Course;Output
On executing the above command, we will get the below result:
The above command is similar to the below command, where we use the INNER JOIN clause instead of the Natural Join Keyword.
SELECT *
FROM Course_categories
INNER JOIN Course USING (Course_id);Output
After executing the above command, we will get the below command:
In PostgreSQL Natural Join, it does not seem necessary to describe the Join clause as it uses an implicit join condition depends on the Common column.
But we should ignore using the Natural Join whenever it possible because sometimes it may cause an unpredicted outcome.
Let us see one example if we have two standard columns in both tables. So, for this we will take employee and department tables:
Structure of the employee table
We will get to see the structure of employee table by using the Select command as follows:
Select * from employee; Output
After implementing the above statement, we will get the below result:
Structure of the department table
We will get to see the structure of the department table by using the Select command as follows:
Select * from department; Output
After implementing the above statement, we will get the below result:
As we can observe in the above screenshots that the employee and department tables have the same emp_id column, so we can use the Natural Join clause to combine these tables in the below command:
SELECT *
FROM employee
NATURAL JOIN department;Output
On executing the above command, we will get the following output:
In the above output, we will get the empty table because both tables also have another common column called emp_fname, and which cannot be used for the PostgreSQL Natural Join. But the Natural Join condition just uses the emp_fname column.
Overview
In the PostgreSQL Natural Join section, we have learned the following topics:
- In the PostgreSQL Natural Join section, we have learned the following topics:
Leave a Reply