PostgreSQL Joins

In this PostgreSQL tutorial, we will learn about PostgreSQL Joins. Here we will learn how to use joins in PostgreSQL and PostgreSQL Joins Types.

PostgreSQL Joins

PostgreSQL joins are used to merge columns from one (self-join) or more tables based on the values of the common columns between related tables. The standard columns are generally the primary key columns of the first table and the foreign key columns of the second table.

It is used to combine columns from more than one table according to the data of the standard columns between connected tables. Generally, the standard columns of the first table are primary key columns, whereas the columns of the second table are foreign key columns.

PostgreSQL supports various types of joins, including inner join, left join, right join, full outer join, cross join, natural join, and a special type of join known as a self-join. We will further explain these types in the next topic. Let’s understand joins by an example.

Example

Firstly, we will create two tables. Here, we will create and insert two different tables, where we will perform actions on several types of joins. In the example shown below, we will use the CREATE command to create two tables i.e. Best_motorcycle and Sports_motorcycle.

CREATE TABLE Best_motorcycle (  
T_ID INT PRIMARY KEY,  
street_bikes_names VARCHAR (250) NOT NULL);
  
CREATE TABLE Sports_motorcycle (  
P_ID INT PRIMARY KEY,  
sports_bikes_names VARCHAR (250) NOT NULL); 

INSERT INTO Best_motorcycle (T_ID, Street_bikes_names)
 VALUES
 (1, 'HondaTrail 125'),
 (2, 'Ducati V4S'),
 (3, 'Suzuki VanVan 200'),
 (4, 'BMW G310R'),
 (5, 'KTM RC 390');

INSERT INTO Sports_motorcycle (P_ID, Sports_bikes_names)
 VALUES
 (1, 'BMW S1000RR'),
 (2, 'Ducati V4S'),
 (3, 'Suzuki Hayabusa'),
 (4, 'KTM RC 390'),
 (5,'Yamaha YZF-R1M');

After executing the above query, I got the expected output as shown in the screenshot below.

Let’s check all motorcycles using the SELECT command.

The above tables have some similar motorcycles which are Ducati V4S and KTM RC 390.

Also, read: Drop Database PSQL

PostgreSQL Joins Types

In this, we will learn about various types of joins in PostgreSQL, which consist of an inner join, left join, right join, and full outer join.

We have some special Postgresql joins which are cross join, natural join and self join.

Inner join

An INNER JOIN in Postgresql creates a new result table by combining column values of two tables (table1 and table2) based upon the join conditions. The query compares each row of both the tables to find all pairs of rows, which satisfy the conditions.

When it’s satisfied, column values for each matched pair of rows of both the tables are combined into a new resultant row. The syntax for the INNER JOIN :

Syntax

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

For Example:

We will take the above tables (Street_motorcycle and Sports_motorcycle) to understand the PostgreSQL inner join.

The below command will join the first table (Street_motorcycle) with the second table (Sports_motorcycle) by matching the values in the street_bikes_name and sports_bikes_name columns.

SELECT T_ID, street_bikes_names, P_ID, sports_bikes_names  
FROM Best_motorcycle  
INNER JOIN Sports_motorcycle 
ON street_bikes_names= sports_bikes_names;  

Let’s check the output of the above query, which is expected as shown in the screenshot below.

The inner join is used to check each row in Table A Best_motorcycle.
It equates the records in the street_bike_name column with records in the sports_bike_name column of each row in Table B Sports_motorcycle.

If these records are the same, then the inner join creates a new row containing columns from both tables and enhances the particular row into the output.

Left Join / Left Outer Join

This join type in PostgreSQL will return all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the condition is satisfied. The syntax for the outer join:

Syntax

SELECT columns
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;

Example

Here, the left join condition selects the records from the left table Best_motorcycle, and it equates the values in the street_bikes_names column with the values in the sports_bikes_names column from the Sports_motorcycle table.

SELECT t_ID, street_bikes_names, p_ID, sports_bikes_names  
FROM best_motorcycle  
LEFT JOIN Sports_motorcycle  
ON Best_motorcycle.street_bikes_names = Sports_motorcycle.sports_bikes_names;  

After executing the above query, I got the expected output as shown in the screenshot below.

If these records are the same, then the left join will create a new row, which has the columns of both tables and will add the particular row to the result, as we can see Row1 and Row2 in the above output.

Let’s suppose that if the values are not the same, then the left join also generates a new row, which involves the columns from both tables and adds them to the outcome.

But it will fill the columns of the right table Sports_motorcycle with null as we can see Row3, Row4, and Row5 in the output.

Right Outer Join

This join type in Postgresql will return all rows from the RIGHT-hand table described in the ON condition and only those rows from the other table where the join conditions are matched. The syntax for the right outer join is shown below.

Syntax

SELECT columns
FROM table1
RIGHT OUTER JOIN table2
ON table1.column = table2.column;

Example

The below command is used to represent the working of Right join where we are joining the Best_motorcycle table with the Sports_motorcycle table.

SELECT t_ID, street_bikes_names, p_ID, sports_bikes_names  
FROM best_motorcycle  
RIGHT JOIN Sports_motorcycle  
ON street_bikes_names= sports_bikes_names;  

After executing the above query, I got the expected output as shown in the below screenshot.

In the above output, the RIGHT JOIN equates each value in the Sports_bikes_names column of every row in the Sports_motorcycle table with each value in the street_bikes_name column of all rows in the best_motorcycle table.

If these values are the same, then the right join will generate a new row, which will carry the columns from both tables Best_motorcycle and Sports_motorcycle.

Assuming that if the values are not the same, then the right join also produced a new row, which involves the columns from both tables and enhances it to the output.

Full Outer Join

This type of join in Postgresql returns all rows from the left side of the table and right side of the table with a null value in the place where the join condition is unmatched.

Syntax

The syntax for the full outer join:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Example

The below command is used to represent the working of the Full Outer join to join the Best_motorcycle table with the Sports_motorcycle table.

SELECT t_ID, street_bikes_names, p_ID, sports_bikes_names  
FROM Best_motorcycle  
FULL OUTER JOIN Sports_motorcycle  
ON street_bikes_names= sports_bikes_names;  

Let’s check the output for the above query.

Cross join

With the help of this join in Postgresql, we can make a Cartesian Product of rows in more than two tables. Basically, it allows us to combine all the probability of the multiple tables and will return the output, which contain each row from all the selected tables. 

It is dissimilar from other join clauses for example LEFT JOIN and INNER JOIN, the CROSS JOIN is not having a join predicate.

A predicate is a condition expression that will evaluate a boolean value, either true or false. Predicates are used in a SELECT statement’s WHERE clause or HAVING clause to decide which rows are compatible with a particular query.

If we perform a CROSS JOIN of two tables TABLE1 and TABLE2.
Suppose, TABLE1 has ‘a’ rows and TABLE2 has ‘b’ rows, the result will be axb rows. For example, the TABLE1 has 100 rows and TABLE2 has 100 rows, the result will be 100 x 100 = 10000 rows.

If we use the asterisk (*) in the select list, the result will consist of all the common columns. Let’s check the syntax.

Syntax

SELECT select_list
FROM TABLE1
CROSS JOIN TABLE2;

or we can also use this syntax.

SELECT select_list
FROM TABLE1, TABLE2;

We can also implement cross join through the INNER JOIN clause with a condition that always evaluates to true to simulate the cross join. Let’s check the syntax.

SELECT *
FROM TABLE1
INNER JOIN TABLE2 ON true;

We will implement this query with the help of our tables which we have already made i.e. Best_motorcycle and Sports_motorcycle. Let’s check the query.

SELECT *
FROM Best_motorcycle
CROSS JOIN Sports_motorcycle;

Now let’s check the output.

Natural join

In Postgresql, when multiple tables are joined, and as an output, we will get the new rows, which is intended to join the columns for each of the tables in the natural join. And it is also used to combine the tables, which creates an implicit join depending on similar column names in the combined tables.

Basically, the natural join will make a temporary table for a set of rows to work on some (more than two) tables. And those tables are described in the join condition, and anyhow has one mutual column, and these standard columns will have a relation between them. Let’s check the syntax.

Syntax

SELECT select_list
FROM TABLE1
NATURAL [INNER, LEFT, RIGHT] JOIN TABLE2;

A natural join could be an inner join, left join, or right join. If we don’t describe a join explicitly e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN, PostgreSQL will use the INNER JOIN by default.

If we use the asterisk (*) in the select list, the result will consist of all the common columns, which are the columns from both tables that have the same name. And, every column from both tables that is not a common column.

Example

We will implement the example with the help of the previous tables, which we have already made Best_motorcycle and Sports_motorcycle. Let’s check the query first.

SELECT * FROM sports_motorcycle
NATURAL JOIN best_motorcycle;

Let’s check the output now.

Self join

In Postgresql, we must know that there is no such keyword as SELF JOIN, but it is implemented through LEFT JOIN, RIGHT JOIN or INNER JOIN using aliases. Basically, a self-join is a regular join that joins a table to itself.

In practice, we generally use a self-join to query hierarchical data or to compare rows within the same table. To form a self-join, we describe the same table twice with different table aliases and provide the join predicate after the ON keyword.

Let’s check the syntax for it.

Syntax

SELECT select_list
FROM table_name table1
INNER JOIN table_name table2 ON join_predicate;

we can also use the below syntax.

SELECT select_list
FROM table_name table1
LEFT JOIN table_name table2 ON join_predicate;

we can also use the below syntax.

SELECT select_list
FROM table_name table1
RIGHT JOIN Table_name table2 ON join_predicate;

We will implement this with the help of the previous tables, which we have made. Now let’s implement the query with LEFT JOIN.

Example

SELECT *  
FROM best_motorcycle  
LEFT JOIN Sports_motorcycle  
ON Best_motorcycle.street_bikes_names = Sports_motorcycle.sports_bikes_names;

Now let’s check the output.

EQUI Join

The EQUI join is a basic join Postgresql which gives us a way of joining two tables based on primary key or foreign key relationship. We have created the tables novel and cost so we will simply add the query. Let’s check the query.

SELECT *
FROM novel 
JOIN cost ON novel.id = cost.id;

Let’s check the output now.

Theta Join

A theta joins in Postgresql permits one to join two tables based on the condition that is defined by theta. This type of join works with all comparison operators. In most cases, the theta join is associated with an inner join.

The theta join is the most fundamental type of JOIN which returns all rows from the tables where the JOIN condition is satisfied. Let’s check the syntax now.

Syntax

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Example

Let’s check the query for the tables that we have created, novel and cost.

SELECT * 
FROM novel 
INNER JOIN Cost 
ON Novel.id = Cost.id;  

Let’s check the output now.

Read: PostgreSQL multiple joins

In this PostgreSQL tutorial, we have learned about Postgresql Joins. Here we have learned how to use joins in PostgreSQL.

You may also like to read the following articles.

• How to create a view in PostgreSQL
• Postgresql Delete Row
• Postgresql For Loop + Examples
• Postgresql create user with password
• PostgreSQL Update Join + Examples
• How to set user password in PostgreSQL