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.