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:
1 | $ sudo -u postgres psql |
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:
1 | SELECT * FROM [table1] NATURAL [LEFT, INNER, RIGHT] JOIN [table2]; |
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:
1 2 3 | CREATE TABLE employees (emp_id serial PRIMARY KEY, name VARCHAR(20) NOT NULL ); |
1 2 3 4 5 6 | CREATE TABLE departments (dept_section serial PRIMARY KEY, dept_name VARCHAR (20) NOT NULL, emp_id INT NOT NULL, FOREIGN KEY (emp_id) REFERENCES employees (emp_id) ); |
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:
1 2 | INSERT INTO TABLE_NAME (column_1, column_2)..., VALUES (values_1,values_2)..., |
Following is an actual example using the INSERT INTO
statement:
1 2 | INSERT INTO employees (name) VALUES ('jorry'),('sammy'),('eugene'),('kimppy'),('cris'); |
This should produce the following results:
1 2 3 4 5 6 7 8 9 | INSERT INTO departments (dept_name, emp_id) VALUES ('accounting',1), ('finance',1), ('billing',2), ('payroll',2), ('marketing',3), ('human_resourse',4), ('collection',5), ('messenger',5); |
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:
1 2 | SELECT * FROM departments NATURAL JOIN employees; |
The results of the above SELECT
statement with a NATURAL JOIN
should look like the following:
1 2 3 4 5 6 7 8 9 10 | emp_id | dept_section | dept_name | name --------+--------------+----------------+-------- 1 | 1 | accounting | jorry 1 | 2 | finance | jorry 2 | 3 | billing | sammy 2 | 4 | payroll | sammy 3 | 5 | marketing | eugene 4 | 6 | human_resourse | kimppy 5 | 7 | collection | cris 5 | 8 | messenger | cris |
PostgreSQL left join
Following is an example for using the NATURAL JOIN
when specifying the LEFT JOIN
function:
1 | SELECT * FROM departments LEFT JOIN employees ON departments.emp_id = employees.emp_id; |
The above statement should produce the following table:
1 2 3 4 5 6 7 8 9 10 | dept_section | dept_name | emp_id | emp_id | name --------------+----------------+--------+--------+-------- 1 | accounting | 1 | 1 | jorry 2 | finance | 1 | 1 | jorry 3 | billing | 2 | 2 | sammy 4 | payroll | 2 | 2 | sammy 5 | marketing | 3 | 3 | eugene 6 | human_resourse | 4 | 4 | kimppy 7 | collection | 5 | 5 | cris 8 | messenger | 5 | 5 | cris |
PostgreSQL right join
Following is another example of the NATURAL JOIN
function, but this time specifying the RIGHT JOIN
option:
1 | SELECT * FROM departments RIGHT JOIN employees ON departments.emp_id = employees.emp_id; |
This should produce the following results:
1 2 3 4 5 6 7 8 9 10 | dept_section | dept_name | emp_id | emp_id | name --------------+----------------+--------+--------+-------- 1 | accounting | 1 | 1 | jorry 2 | finance | 1 | 1 | jorry 3 | billing | 2 | 2 | sammy 4 | payroll | 2 | 2 | sammy 5 | marketing | 3 | 3 | eugene 6 | human_resourse | 4 | 4 | kimppy 7 | collection | 5 | 5 | cris 8 | messenger | 5 | 5 | cris |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started