PostgreSQL Natural Join

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.

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:

The below command is used to create the Course table:

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:

In the below command, we are inserting the values in the Course table:

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:

Table1: Course_categories

Output

After implementing the above command, we will get the following data from the Course_categories table:

PostgreSQL Natural Join

Table2: Course

Output

After executing the above command, we will get the following records from the Course table:

PostgreSQL Natural Join

The below query uses PostgreSQL Natural Join clause to combine records from Course and Course_categories tables.

Output

On executing the above command, we will get the below result:

PostgreSQL Natural Join

The above command is similar to the below command, where we use the INNER JOIN clause instead of the Natural Join Keyword.

Output

After executing the above command, we will get the below command:

PostgreSQL Natural Join

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:

Output

After implementing the above statement, we will get the below result:

PostgreSQL Natural Join

Structure of the department table

We will get to see the structure of the department table by using the Select command as follows:

Output

After implementing the above statement, we will get the below result:

PostgreSQL Natural Join

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:

Output

On executing the above command, we will get the following output:

PostgreSQL Natural Join

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:





Latest Courses