PostgreSQL Natural Join Operation

Introduction

In PostgreSQL, a joined table is created from two other tables based on the rules of the particular type of join operation. Of the various types of join operation in Postgres, the NATURAL join is a special type of INNER join operation where PostgreSQL returns records from Postgres tables by grouping the records together. These groupings are based on matches found the column values of the various tables. This PostgreSQL tutorial will explain how to use the PostgreSQL natural join operation to query data from multiple tables in a database.

Prerequisites

• PostgreSQL and the command-line interface must be properly installed and configured on the local machine in order to use the PostgreSQL natural join operation to query data.

• Possess a basic knowledge in database management systems, particularly PostgreSQL and the desire to expand that knowledge base.

• The PostgreSQL client server is required for executing queries in the PostgreSQL database cluster. Executing the psql -V command will return the currently installed version number of the interactive shell.

Open command line interface

Access to the command-line interface must be established in order to test the various syntax in PostgreSQL. Executing the following command will bring up the psql command console in admin mode:

PostgreSQL natural join

A NATURAL JOIN groups records together based on similarities with column values found in other tables. A NATURAL JOIN can be a LEFT JOIN, INNER JOIN or RIGHT JOIN, but the type of join must be specified in the connection or PostgreSQL will use the INNER JOIN operation by default.

Following is the syntax for a Natural Join:

Note that the use of an asterisk (*) in the select list will produce results containing the following fields:

• The common fields in table where both columns have a similar name.
• The unique column in both tables that contain different column names.

PostgreSQL create table

To use the Natural Join function, create two sample tables named employees and departments in PostgreSQL.

Execute the following two CREATE TABLE statement syntax to create the “employees” and “department” tables:

Note that the departments table emp_id is a foreign key and references employees as the primary key of the PostgreSQL table. Since both tables have _empid columns, either can be used to demonstrate the PostgreSQL natural join operation.

PostgreSQL insert data

With the tables created, one or more rows can now be inserted into the employees and departments PostgreSQL table. The following INSERT INTO statement is used to insert records into a table:

Following is an actual example using the INSERT INTO statement:

This should produce the following results:

Note that the _tablename is placed after the INSERT INTO statement and followed by its column name and values and are organized in identical order.

PostgreSQL natural join example

This section demonstrates joining the employees and departments PostgreSQL tables using NATURAL connections:

The results of the above SELECT statement with a NATURAL JOIN should look like the following:

PostgreSQL left join

Following is an example for using the NATURAL JOIN when specifying the LEFT JOIN function:

The above statement should produce the following table:

PostgreSQL right join

Following is another example of the NATURAL JOIN function, but this time specifying the RIGHT JOIN option:

This should produce the following results:

Conclusion

This PostgreSQL tutorial explained how to use the PostgreSQL natural join operation to query data from multiple tables in a database. The tutorial covered how to access the command line interface to test the various syntax in PostgreSQL and then explained some of the options for using the PostgreSQL natural join function. The article then covered how to use the Natural Join function and created two sample tables. The tutorial then explained how to insert data using actual examples for the Insert Into statement and covered using the natural join function with both left and right join examples. Remember that whenever grouping records together, based on similarities with column values, if the type of join is not specified in the connection PostgreSQL will default to the INNER JOIN option.