Postgresql Joins – How to use

In this PostgreSQL tutorial, we are going to learn about Postgresql Joins. Here we will learn how to use joins in PostgreSQL, and we will also cover the following list of topics.

• Postgresql joins
• Postgresql joins types
• Postgresql joins for update
• Postgresql multiple joins
• Postgresql multiple joins same table
• Postgresql table joins
• Postgresql nested loop joins
• Postgresql multiple inner joins
• Postgresql update multiple joins

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 common columns are generally the primary key columns of the first table and 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 second table columns are foreign key columns.

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

Firstly we will create two tables. Here, we will be creating and inserting 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');

Now we will check the output of the above query.

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 consists 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 :

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.

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:

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

Here is the code for the above syntax.

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;  

Let’s check the output of the above query.

In the above output, 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.

If these records are the same, then the left join will create a new row, which is having 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, 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.

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

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;  

Let’s check the output for the above query.

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.

The syntax for the full outer join:

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

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.

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.

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, which is not a common column.

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.

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.

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.

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

Let’s check the query for the tables which 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 date to string

Postgresql joins for update

We can update the records stored in the table in different ways. Postgresql provides an option of UPDATE feature as well. In some cases, we want to update one table’s records to support another table’s records.

For joining another table in the statement, we will have to explain the PostgreSQL FROM clause with the joined table, along with we need to describe the PostgreSQL WHERE clause with a JOIN condition. We will add the SET clause and have to describe the PostgreSQL FROM clause instantly after it.

We have already discussed this in the top “PostgreSQL JOINS Update“.

Postgresql multiple joins

Now we will learn how we can add multiple joins in Postgresql. Basically, it is a query that contains similar or different join types, which are used more than once.

Hence, we gain the ability to combine multiple tables of data in order to overcome relational database issues. We will understand this with the help of an example in which we will create three tables and will apply multiple joins in it. Let’s check the query for three tables.

select * from emp;
select * from customers;
select * from payment;

Let’s check the output of the three tables.

Now we tend to represent a three-table join example, so let’s consider the situation where you need to how much each customer has paid to which employee. We need a query that can get this data quickly and effectively. Let us break the query into two parts.

Firstly, we should know which customer paid which employee. This can be achieved through a single join query on the customer and payment table. Let’s check the query.

SELECT cust.name, pay.amount
FROM Customers cust
INNER JOIN Payment pay ON pay.customerID = cust.customerID ;

This query will give us outcomes about all customers and their respective payments. Now secondly the main query is to analyze which employee has received which payment. This can be attained through a common join query on payment and employee tables as.

SELECT emp.name , pay.amount
FROM Employees emp
INNER JOIN Payment pay ON pay.employeeID = emp.employeeID ;

Now we will see that both above queries you will notice are similar just referenced on to different tables. Now if we want to check which customer paid to which of the employees in that case we only have to merge the above two queries with another join statement. This query will point to three tables and two join statements. Let’s see the query.

SELECT
              cust.name AS cust_name,
              emp.name AS emp_name,
              pay.amount
FROM
              Customer cust
              INNER JOIN Payment pay ON pay.customerID = cust.customerID
              INNER JOIN Employees emp ON pay.employeeID = emp.employeeID;

This query will provide us with the outcome of how much customers paid to each employee. This query will firstly note the customer and payment table with one join and then it will unite the resulting table with the employee table as a second join. Here is the output.

Also, check: Postgresql function return table

Postgresql table joins

Now we will learn how joins between tables can be used in the same table in Postgresql using the psql tool. In Postgresql, we have learned that our queries have only accessed one table at a time.

Now we will check that how queries can access multiple tables at once, or we will access the same table in such a way that multiple rows of the table are being processed at the same time. A query that accesses multiple rows of similar or different tables at once is known as a join query.

Let’s take an example say we will list all the weather records together with the location of the connected city. So, we will compare the city column of each row of the weather table with the name column of all rows in the city table and will select the pairs of rows where these values match. Let’s understand this by creating the tables.

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);
CREATE TABLE cities (
    name            varchar(80),
    location        point
);
INSERT INTO weather VALUES ('Austin', 46, 50, 0.25, '2021-11-01');
INSERT INTO cities VALUES ('Austin', '(-194.0, 53.0)');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('Austin', 43, 57, 0.0, '2021-11-01');
INSERT INTO weather (date, city, temp_hi, temp_lo)
    VALUES ('2021-11-01', 'Dallas', 54, 37);

Here is the query for joining with tables

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

Let’s check the output of the above query.

We will observe two things about the output. The first is that there is no result row for the city of Dallas. This is because there is no matching entry in the cities table Dallas, so the join ignores the unmatched rows in the weather table.

There are two columns in the table that have the city name. This is correct because the lists of columns of the weather and the cities table are integrated. In practice, this is undesirable, though, so we should probably want to list the output columns explicitly rather than using *.

Read: Create a stored procedure in PostgreSQL

Postgresql update multiple joins

Now we will learn how we can update multiple joins in Postgresql. We are not able to use a table alias in the FROM clause as the target table in the UPDATE clause. The one table to be updated comes right once the UPDATE keyword if we tend to ignore a possible ONLY keyword in between.

We will add an alias there if required. That’s the instant cause of our error message, however, there’s more. The column that is to be updated is often from the one table to be updated and can’t be table-qualified.

The target table is not repeated in the FROM clause except for some of the special cases like in update with a left outer self join ignored This optional addition can avoid pointless costs by suppressing updates that do not change anything.

We don’t require any other join. With this query, we can update a column in one table with values from a column from other tables. And, it updates only when the WHERE condition is true.

We will take an example with two tables students and course Let’s check the query for the table.

select * from STUDENTS;
select * from COURSE;

Let’s check the output.

Let’s check the query to update multiple joins.

UPDATE students
SET course_name = course.course_name
FROM course, teachers
WHERE students.course_id = course.course_id
AND course.teacher_id = teachers.teacher_id;

Let’s check the output now.

Read: PostgreSQL list databases

Postgresql nested loop joins

A join among two tables results in each pair of rows where some condition is true. Postgresql will return every pair of rows from table1 and table2 where the join condition is true.

How a sequential scan is the most common way to fetch rows from the table, nested loops are the most common way for Postgresql to perform a join.

If Postgresql will execute the query with a nested loop, it can be done by iterating all of the entries in table1, iterating through all of the entries in table2, and then expending a row when those pair of rows from table1 and table2 will satisfy the filter condition. Let’s check the syntax.

SELECT *
 FROM table1, table2
WHERE <filter>;

Let’s check the code for the above syntax in which we have created tables and through which we have implemented the query.

create table human1 (name text,age int);
create table human2 (name text,age int);
insert into human1 values  ('Jack',16), ('Alex',12), ('Olivia',13), ('Kendall',14);
insert into human2 values  ('Olivia',13), ('Kendall',14), ('William',15), ('Kyle',16);

SELECT * FROM human1, human2
WHERE human1.age = human2.age
  AND human1.age > 30;

SELECT * FROM human1, human2
WHERE human1.age = human2.age
  AND human1.age > 15;

Let’s check the output of the above query.

If there is an index on the human2.age field, Postgres can execute an index join. Postgres can first fetch all rows from the people table for people over the age of 20 and 15 (using either a sequential scan, index scan, or bitmap index scan).

Then Postgres can iterate through each person over 20 and 15 which uses the index on human2.age to quickly lookup all human2 that belong to that person and then add that pair of rows to the result. This is only O(M*log(N)) and is generally more productive than O(M*N) naive nested loop.

Read: PostgreSQL Data Types + Examples

Postgresql multiple inner joins

Now we will understand how multiple inner joins are implemented in Postgresql using the psql tool. In PostgreSQL, the INNER JOIN will select all rows from both the tables as long as the condition is satisfied. This will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same.

Inner Join is the procedure of retrieval of data from multiple tables based on a required condition and necessary conditions are that there must be either common columns or matched columns among two tables of the database and the data types of columns should be similar.

Now we will learn multiple inner joins by creating an example in which we will create three tables and then we will apply the inner joins in it. Let’s check the query for the first table.

CREATE TABLE students
( 
 stu_id varchar(10),
 stu_name varchar(20),
 course_id varchar(10),
 branch varchar(20),
 teacher_id varchar(10)
);
CREATE TABLE course
(
  course_id varchar(10),
  course_name varchar(20)
);
CREATE TABLE teachers
(
  teacher_id varchar(10),
  teacher_name varchar(20)
);

Now we will insert values in these tables.

INSERT INTO STUDENTS VALUES
('191','Smith','RY103','Ruby', 'P4002'),
('192','Olivia','AD101','Andriod', 'P4001'),
('193','Charles','JA104', 'Java','P4004'),
('194','William','PY102','Python', 'P4003');

INSERT INTO COURSE VALUES
('AD101', 'Andriod'),
('PY102', 'Python'),
('RY103', 'Ruby'),
('JA104', 'Java');

INSERT INTO TEACHERS VALUES
('P4001', 'Lucas'),
('P4002', 'William'),
('P4003', 'James'),
('P4004', 'Emma');

Let’s check the output.

Let’s check the syntax for applying multiple inner joins.

SELECT column_name1,column_name2,..
FROM table_name1
INNER JOIN 
table_name2
ON condition_1
INNER JOIN 
table_name3
ON condition_2
INNER JOIN 
table_name4
ON condition_3
.
.
.

Let’s check the query for the above syntax followed by the output.

SELECT stu_id, stu_name,course.course_id,course.course_name,
teacher.teacher_name
FROM students  
INNER JOIN  
course  
ON  
students.course_id = course.course_id
INNER JOIN  
teachers  
ON  
students.teacher_id = teachers.teacher_id;

Let’s check the output.

Read: Postgresql date between two dates

Postgresql multiple joins same table

In this topic, we will learn that how multiple joins are implemented in the same table. We will understand this with the help of an example in which we will create tables and will put more than one join in it. Let’s check the example for the table.

CREATE TABLE products (  
    id INT PRIMARY KEY,  
    name VARCHAR  
);  
CREATE TABLE propertyy (  
    id INT PRIMARY KEY,  
    name VARCHAR  
);  
CREATE TABLE values (  
    id INT PRIMARY KEY,  
    values VARCHAR  
);  
CREATE TABLE products_values (  
    products INT NOT NULL REFERENCES products(id),  
    propertyy INT NOT NULL REFERENCES propertyy(id),  
    values INT NOT NULL REFERENCES values(id)  
);  
INSERT INTO products (id, name) VALUES (1, 'products1'), (2, 'products2');  
INSERT INTO propertyy (id, name) VALUES (1, 'prop1'), (2, 'prop2');  
INSERT INTO values (id, values) VALUES (1, 'val1'), (2, 'val2');  
INSERT INTO products_values (products, propertyy, values) VALUES (1, 1, 1), (2,2,2); 

Let’s check the output of the above query.

Now we will implement a query in which we will create more than one join using the same table. In the example explained we have explained left join two times. Let’s have a look at the query.

SELECT p.name as name, v1.values as values_1, v2.values as values_2 
FROM products p
LEFT JOIN products_values iv ON iv.products = p.id
LEFT JOIN values v1 ON v1.id = iv.values AND v1.id = (
  SELECT v.id FROM value v 
  JOIN propertyy p ON p.id = iv.propertyy 
    AND p.name = 'prop1' AND v.id = v1.id AND v.id = iv.values
)
LEFT JOIN values v2 ON v2.id = iv.values AND v2.id = (
  SELECT v.id FROM values v 
  JOIN propertyy p ON p.id = iv.propertyy 
    AND p.name = 'prop2' AND v.id = v2.id AND v.id = iv.values
);

Let’s check the output of the above query.

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

In this PostgreSQL tutorial, we have learned about Postgresql Joins. Here we have learned how to use joins in PostgreSQL, and we have also covered the following list of topics.

• Postgresql joins
• Postgresql joins types
• Postgresql joins for update
• Postgresql multiple joins
• Postgresql multiple joins same table
• Postgresql table joins
• Postgresql nested loop joins
• Postgresql multiple inner joins
• Postgresql update multiple joins