To store and retrieve data efficiently, databases rely on relationships between tables. This enables users to access and use the datasets at any given time. Relational databases like PostgreSQL provide query support for accessing these datasets. To access related data from multiple tables, JOINS can be used in PostgreSQL. NATURAL JOIN is the type of JOIN supporting Multiple JOINs but uses INNER JOIN by default.
This guide will explain how to use NATURAL JOIN in PostgreSQL.
How to Use NATURAL JOIN in PostgreSQL?
The NATURAL JOIN includes INNER, LEFT, and RIGHT JOINs within the single JOIN to combine multiple tables. If the user does not specify any of these keywords, the NATURAL JOIN automatically uses the INNER JOIN as a default JOIN. To apply a NATURAL JOIN, the tables must have one common column name between the two of them.
The syntax to use the NATURAL JOIN is as follows:
SELECT <Columns> FROM <TableA> NATURAL [INNER, LEFT, RIGHT] JOIN <TableB>;
In the above code block:
- The SELECT keyword is used to select columns or fields of the table mentioned after the FROM keyword.
- The NATURAL keyword can be followed by these [INNER, LEFT, RIGHT] JOINs containing the name of the second table.
Example 1: Use NATURAL JOIN to Combine Tables
Use the following query to get the data of the person table:
SELECT * FROM person;
Running the above code with “*” displays all the data/records of the person table:
Use the following code to get data from the vehicle table:
SELECT * FROM vehicle;
The following is the query for the NATURAL JOIN:
SELECT * FROM vehicle NATURAL JOIN person;
The above query will join the vehicle table with the person table and display all records having data in the common field:
Example 2: Use NATURAL JOIN With ORDER Clause
NATURAL JOIN can be used with additional conditions using the ORDER clause as mentioned below:
SELECT * FROM vehicle NATURAL JOIN person ORDER by vehicle.price;
Running the above query will join both the vehicle and person table and display the results according to the price of the vehicle:
That’s all about using NATURAL JOIN in PostgreSQL.
NATURAL JOIN in PostgreSQL databases can be used to join multiple tables containing the same field name. NATURAL JOIN contains INNER, RIGHT, and LEFT JOINs and all of them can be used with it however, by default it uses INNER JOIN. The user can use the ORDER clause with NATURAL JOIN to add a sorting condition to the resultant table. This guide has explained the use of NATURAL JOIN in the PostgreSQL database.