Join Queries In PostgreSQL with Examples


By onjsdev

Join queries help combine different tables in a database. They are mostly used to select data between tables. Understanding join queries can be a little confusing because of their various types, but let's dive into examples of them in postgresql.

We are going to discuss this topic on an example of publishers and readers tables in a blog database. Let's begin with creating these tables.

Set Up Tables Using Postgresql Bash

  • Create Table

For the sake of simplicity, we will not have a lot of columns in the tables. The reader table has two columns, publisher table has three columns. To create a readers and publishers table, you can use the following query.

CREATE TABLE reader(id INT PRIMARY KEY,name VARCHAR(64));
CREATE TABLE publisher(id INT PRIMARY KEY,name VARCHAR(64),number_of_article INT);
  • Insert Data Into Tables

Once you create the table, we can insert rows to the tables. We assume that a reader can also be a publisher, which will make understanding join queries clear.

# reader
INSERT INTO reader VALUES(1,'john');
INSERT INTO reader VALUES(2,'jane');
INSERT INTO reader VALUES(3,'julia');

# publisher
INSERT INTO publisher VALUES(1,'tom',2);
INSERT INTO publisher VALUES(2,'jane',4);
INSERT INTO publisher VALUES(3,'julia',3);

Types of Join Queries In PostgreSQL

Join queries in postgreslq have different types, now, we can get into these types to see why we need join queries with the help of following examples.

Inner Join

Inner join queries return the matching common values of the tables. For example, if we need users who are both readers and publishers, we can use a select query and an inner join query.

The following query returns the name and number of the articles of people who are both the reader and publisher.

SELECT reader.name, publisher.number_of_article 
FROM reader INNER JOIN publisher 
ON reader.name = publisher.name;
-- output
 name  | number_of_article 
-------+-------------------
 jane  |                 4
 julia |                 3
(2 rows)

Left Join

Left represents the first table in a query and this query returns all data in the left table as well as common data in the two tables.

SELECT reader.name as reader_name, publisher.name as publisher_name
FROM reader 
LEFT JOIN publisher 
ON reader.name = publisher.name;

In the example above, the left table is the reader table so that we could get all data in this table and also the common data in the two tables. As John is not a publisher this value has been set as null.

-- output
 reader_name | publisher_name 
-------------+----------------
 john        | 
 jane        | jane
 julia       | julia
(3 rows)

Right Join

Nothing changes compared to left join query, we can get all data from right table and common data in two tables.

SELECT reader.name as reader_name, publisher.name as publisher_name 
FROM reader 
RIGHT JOIN publisher 
ON reader.name = publisher.name;

In the example above, the right table is the publisher table so that we could get all data in this table and also the common data in the two tables. As Tom is not a reader this value has been set as null.

-- output
 reader_name | publisher_name 
-------------+----------------
 jane        | jane
 julia       | julia
             | tom
(3 rows)

Full Join

The full join combines the all rows in the left table and the right table. It is set as null for values that are not matching.

SELECT * FROM reader 
FULL JOIN publisher 
ON reader.name = publisher.name;

As Jane and Julia are in two tables, we got these rows. We also get existing information of john from the left table and tom from the right table as seen below.

-- output
 id | name  | id | name  | number_of_article 
----+-------+----+-------+-------------------
  1 | john  |    |       |                  
  2 | jane  |  2 | jane  |                 4
  3 | julia |  3 | julia |                 3
    |       |  1 | tom   |                 2
(4 rows)

Conclusion

In this article, we have covered join queries in PostgreSQL with examples, which are mostly used with foreign keys to select data in combined different tables.

Thank you for reading.