Introduction to PostgreSQL NATURAL JOIN
In simple terms, the join is where two or more tables are joined, and as a result, we will get the new set of rows for combined columns requested for each of the tables. A joint operation creates an implicit join clause on the common columns for the tables that we have defined in the join clause. A Join operation actually creates a temporary table for a set of rows to work on two or more tables. The tables defined in the join clause should have at least one common column, and the common columns should have a relation between them.
Syntax
SELECT *
FROM table_name_1
NATURAL [INNER, LEFT, RIGHT] JOIN table_name_2;
Explanation: The table_name_1 and table_name_2 are the two tables on which we perform a joining to get the combined result for the columns having the same name in both tables. The table_name_1 is generally read as a left (first) table, and The table_name_2 is generally read right (second) table.
Joins in PostgreSQL NATURAL JOIN
NATURAL JOIN operation can be any of the following:
- Inner join
- Left join
- Right join
The PostgreSQL uses the INNER JOIN by default if we do not define a name of join explicitly as INNER JOIN, LEFT JOIN, or RIGHT JOIN.
How NATURAL JOIN works in PostgreSQL?
If we create a SQL statement having an asterisk (*) instead of column names in the SELECT clause with the NATURAL JOIN operation, then the columns will be considered in the following order for an asterisk (*) :
- All the common columns from the left (first) table and right (second) table.
- Every column in the left (first) table which is not common with the right (second) table columns.
- Every column in the second (right) table which is not common with the left (first) table columns.
In PostgreSQL, the NATURAL JOIN is an INNER JOIN where we combine all columns with the same name in both tables.
Examples to Implement NATURAL JOIN in PostgreSQL
Let’s create two tables named’ transaction’ and ‘invoices’ to understand the PostgreSQL NATURAL JOIN examples.
Example #1
The following CREATE TABLE statements will create the transaction and invoices table.
Code:
CREATE TABLE transaction (
transaction_id serial PRIMARY KEY,
transaction_data VARCHAR (256) NOT NULL
);
CREATE TABLE invoices (
invoice_id serial PRIMARY KEY,
transaction_id INT NOT NULL,
invoice_data VARCHAR (256) NOT NULL,
FOREIGN KEY (transaction_id) REFERENCES transaction (transaction_id)
);
- The transaction_id is the primary key of the transaction table, which is referred to as a foreign key for the invoices table. So while performing natural join operation, we will use the transaction_id column as it is the common column for both tables. The transaction may have zero or more invoices, and the invoice will belong to one and only one transaction.
Now insert some data into the transaction and invoices tables using INSERT statement as follows:
Code:
INSERT INTO transaction (transaction_data)
VALUES
('Purchase of Mobile'),
('Purchase of PC'),
('Purchase of Headphone');
INSERT INTO invoices (invoice_data, transaction_id)
VALUES
('Purchase of Mobile', 1),
('Purchase of Mobile', 1),
('Purchase of PC', 2),
('Purchase of PC', 2),
('Purchase of Headphone', 3),
('Purchase of Headphone', 3);
- Illustrate the content of the transaction table using the following statement and snapshot.
Code:
select * from transaction;
Output:
- Illustrate the content of the invoices table using the following statement and snapshot.
Code:
select * from invoices;
Output:
- Join the invoices table with the transaction table using the NATURAL JOIN clause:
Code:
SELECT
*
FROM
invoices
NATURAL JOIN transaction;
Illustrate the result of the above statement using the following snapshot.
Output:
- The above statement with the NATURAL JOIN clause similar to the statement with the INNER JOIN clause as follows:
Code:
SELECT
*
FROM
invoices
INNER JOIN transaction USING (transaction_id);
- Illustrate the result of the above statement using the following snapshot.
Output:
- It is not required to define the join clause as the NATURAL JOIN uses an implicit join clause based on the common column.
- But if possible, we should avoid using the NATURAL JOIN because if we are having multiple common columns, it gives us an unexpected result.
Example #2
Consider the following example for two tables named COUNTRIES and CITIES. The following CREATE TABLE statements will create the COUNTRIES and CITIES table.
Code:
CREATE table COUNTRIES
(
country_id serial PRIMARY KEY,
country_name VARCHAR (256) NOT null,
last_updated DATE NULL
);
CREATE table CITIES
(
CITY_id serial PRIMARY KEY,
country_id INT NOT NULL,
city_name VARCHAR (256) NOT NULL,
last_updated DATE null,
FOREIGN KEY (country_id) REFERENCES COUNTRIES (country_id)
);
- Now insert some data into the COUNTRIES and CITIES tables using INSERT statement as follows:
Code:
INSERT INTO COUNTRIES (country_name,last_updated)
VALUES
('India','06-01-2020'),
('US','07-01-2020'),
('CHINA','08-01-2020');
INSERT INTO CITIES (country_id, city_name,last_updated)
VALUES
(1,'Pune','06-02-2020'),
(1,'Mumbai', '07-02-2020'),
(2,'New York', '08-02-2020'),
(2,'Los Angeles', '09-02-2020'),
(3,'Beijing ', '10-02-2020'),
(3,'Shanghai', '11-02-2020');
Code:
select * from COUNTRIES;
- Illustrate the content of the COUNTRIES table using the following statement and snapshot.
Output:
Code:
select * from CITIES;
- Illustrate the content of the CITIES table using the following statement and snapshot.
Output:
- Here, Both COUNTRIES and CITIES tables have two common columns named country_id and last_updated, so to join these tables, we can use the NATURAL JOIN as follows:
Code:
SELECT
*
FROM
COUNTRIES
NATURAL JOIN CITIES;
- In the above statement, the COUNTRIES and CITIES tables having common columns country_id and last_updated. Even if the last_updated column is common, it should not be used in the joint operation.
- However, the last_updated column is getting used in the NATURAL JOIN clause, ending in an unexpected result.
Conclusion
We hope from the above article you have learned how the PostgreSQL NATURAL JOIN works. Also, we have added some examples to demonstrate to you how to use the NATURAL JOIN to query data from two or more tables.
Recommended Articles
This is a guide to PostgreSQL NATURAL JOIN. Here we discuss Syntax to NATURAL JOIN, how does it work, with examples to implement join. You can also go through our other related articles to learn more –
