A PostgreSQL JOIN models logical relationships when retrieving data from multiple normalized database tables. Normalization is a technique for reducing data redundancy, improving efficiency, and speeding up queries in a database. The following list highlights some benefits of using joins in PostgreSQL:
Readability. Developers comprehend JOIN statements faster than nested sub-queries.
Reduced execution time. A JOIN statement that uses indexed columns executes faster.
Flexibility. A JOIN statement uses a single command to fetch and filter data from multiple tables.
This tutorial provides the basic concepts of using different joins when working in a PostgreSQL server.
To proceed with this tutorial:
To better understand how PostgreSQL joins work, create a sample database, add a few tables, and load data by following the steps below:
Log in to the PostgreSQL server as user postgres.
$ sudo -u postgres psql
Enter your password and press ENTER to proceed. Then, execute the following statement to create a sample online_shop database.
postgres=# CREATE DATABASE online_shop;
Output.
CREATE DATABASE
Connect to the new online_shop database:
postgres=# \connect online_shop;
Output.
You are now connected to database "online_shop" as user "postgres".
Create a products table.
online_shop-# CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR (50),
retail_price NUMERIC(5,2)
);
Output.
CREATE TABLE
Populate the products table with sample data.
online_shop-# INSERT INTO products (product_name, retail_price) VALUES ('COOL KICK SPRAY', 4.55);
INSERT INTO products (product_name, retail_price) VALUES ('BEAUTY SOAP', 1.25);
INSERT INTO products (product_name, retail_price) VALUES ('LEMON SHAMPOO', 8.85);
INSERT INTO products (product_name, retail_price) VALUES ('LAVENDER SHOWER GEL', 6.55);
INSERT INTO products (product_name, retail_price) VALUES ('MEN FACIA CREAM', 4.20);
INSERT INTO products (product_name, retail_price) VALUES ('FRESH ENERGY ROLL ON', 4.55);
INSERT INTO products (product_name, retail_price) VALUES ('AFTER SHAVE BALM', 9.35);
Output.
...
INSERT 0 1
Query the products table to verify the data.
online_shop-# SELECT
product_id,
product_name,
retail_price
FROM products;
Output.
product_id | product_name | retail_price
------------+----------------------+--------------
1 | COOL KICK SPRAY | 4.55
2 | BEAUTY SOAP | 1.25
3 | LEMON SHAMPOO | 8.85
4 | LAVENDER SHOWER GEL | 6.55
5 | MEN FACIA CREAM | 4.20
6 | FRESH ENERGY ROLL ON | 4.55
7 | AFTER SHAVE BALM | 9.35
(7 rows)
Create a sales_products table. The sales_products table stores the product_ids associated with each customer's order. The product_id column in the sales_products table refers back to the product_id column in the products table.
online_shop-# CREATE TABLE sales_products (
ref_id SERIAL PRIMARY KEY,
sales_id INTEGER,
product_id INTEGER
);
Output.
CREATE TABLE
Insert sample data into the sales_products table.
online_shop-# INSERT INTO sales_products (sales_id, product_id) VALUES (100731, 1);
INSERT INTO sales_products (sales_id, product_id) VALUES (100732, 2);
INSERT INTO sales_products (sales_id, product_id) VALUES (100733, 3);
INSERT INTO sales_products (sales_id, product_id) VALUES (100734, 8);
INSERT INTO sales_products (sales_id, product_id) VALUES (100735, 1);
INSERT INTO sales_products (sales_id, product_id) VALUES (100736, 6);
INSERT INTO sales_products (sales_id, product_id) VALUES (100737, 10);
Output.
...
INSERT 0 1
Query the sales_products table to ensure the data is in place.
online_shop-# SELECT
ref_id,
sales_id,
product_id
FROM sales_products;
Output.
ref_id | sales_id | product_id
--------+----------+------------
1 | 100731 | 1
2 | 100732 | 2
3 | 100733 | 3
4 | 100734 | 8
5 | 100735 | 1
6 | 100736 | 6
7 | 100737 | 10
(7 rows)
You've set up a database, created some tables, and loaded sample data. Review the type of PostgreSQL joins in the next step.
This tutorial discusses the following basic types of PostgreSQL joins:
Left join.
Right join.
Inner Join.
Here is the basic syntax for creating a PostgreSQL JOIN.
online_shop-# SELECT
SAMPLE_COLUMN_NAMES
FROM SAMPLE_TABLE_1
LEFT|RIGHT|OUTER JOIN SAMPLE_TABLE_2
ON SAMPLE_COLUMN.SAMPLE_TABLE_1 = SAMPLE_COLUMN.SAMPLE_TABLE_2
LEFT|RIGHT|OUTER JOIN SAMPLE_TABLE_3
ON SAMPLE_COLUMN.SAMPLE_TABLE_2 = SAMPLE_COLUMN.SAMPLE_TABLE_3
LEFT|RIGHT|OUTER JOIN SAMPLE_TABLE_4
ON SAMPLE_COLUMN.SAMPLE_TABLE_3 = SAMPLE_COLUMN.SAMPLE_TABLE_4
...
Note the following when working with PostgreSQL joins:
Define the left table after the FROM keyword.
Specify the right table after the LEFT|RIGHT|OUTER JOIN clause.
Use the ON keyword to define related columns and specify a JOIN condition(For instance, =).
The following sections discuss the above terms better using examples.
A PostgreSQL LEFT JOIN statement returns all rows from the left table and any matching rows from the right table. If there are no matching rows in the right table depending on the JOIN condition, PostgreSQL displays an empty string.
For example, suppose you want to retrieve all records from the sales_products table with the actual product names spelled out. The query requires you to link the sales_products and the products table. To achieve that output, model the JOIN statement as follows:
The sales_products is a left table.
The products is a right table.
The product_id is the JOIN column.
With the above model in mind, construct the following JOIN statement.
online_shop-# SELECT
sales_products.ref_id,
sales_products.sales_id,
sales_products.product_id,
products.product_name
FROM sales_products
LEFT JOIN products
ON sales_products.product_id = products.product_id;
In the following output, products `8` and `10` do not have any matching rows in the `products` table. In short, products `8` and `10` are orphaned sales, and PostgreSQL couldn't determine their matching product names.
ref_id | sales_id | product_id | product_name
--------+----------+------------+----------------------
1 | 100731 | 1 | COOL KICK SPRAY
2 | 100732 | 2 | BEAUTY SOAP
3 | 100733 | 3 | LEMON SHAMPOO
4 | 100734 | 8 |
5 | 100735 | 1 | COOL KICK SPRAY
6 | 100736 | 6 | FRESH ENERGY ROLL ON
7 | 100737 | 10 |
(7 rows)
A PostgreSQL RIGHT JOIN statement returns all records from the right table and any other results from the left table that meet the JOIN condition. For example, to return all records from the products table and any matching sales from the sales_products table, model the JOIN as follows:
The products is a left table.
The sales_products is a right table.
The product_id is the JOIN column.
Run the following SQL statement to achieve the JOIN logic above.
online_shop-# SELECT
products.product_name,
sales_products.ref_id,
sales_products.sales_id,
sales_products.product_id
FROM sales_products
RIGHT JOIN products
ON sales_products.product_id = products.product_id;
PostgreSQL displays all records from the products table as shown in the following output. It doesn't matter whether there is a matching sale or not from the sales_products table. The MEN FACIA CREAM, the LAVENDER SHOWER GEL, and the AFTER SHAVE BALM products are not associated with any sales.
product_name | ref_id | sales_id | product_id
----------------------+--------+----------+------------
COOL KICK SPRAY | 1 | 100731 | 1
BEAUTY SOAP | 2 | 100732 | 2
LEMON SHAMPOO | 3 | 100733 | 3
COOL KICK SPRAY | 5 | 100735 | 1
FRESH ENERGY ROLL ON | 6 | 100736 | 6
MEN FACIA CREAM | | |
LAVENDER SHOWER GEL | | |
AFTER SHAVE BALM | | |
(8 rows)
A PostgreSQL INNER JOIN statement returns results that only meet the JOIN condition in both tables. This JOIN finds the commonality (matching data) between multiple tables. For instance, to retrieve only the valid sold products from your database, structure your INNER JOIN statement as follows:
online_shop-# SELECT
sales_products.ref_id,
sales_products.sales_id,
sales_products.product_id,
products.product_name
FROM sales_products
INNER JOIN products
ON sales_products.product_id = products.product_id;
The output below shows that only five records meet the `INNER JOIN` condition.
ref_id | sales_id | product_id | product_name
--------+----------+------------+----------------------
1 | 100731 | 1 | COOL KICK SPRAY
2 | 100732 | 2 | BEAUTY SOAP
3 | 100733 | 3 | LEMON SHAMPOO
5 | 100735 | 1 | COOL KICK SPRAY
6 | 100736 | 6 | FRESH ENERGY ROLL ON
(5 rows)
The previous examples highlight a few use-cases for PostgreSQL joins. In a production environment, you may also use joins in the following scenarios:
Retrieving product names and associated category names.
Tables: products and products_categories.
Returning sales orders and the related customer billing and shipping information.
Tables: sales_orders and customers.
Displaying employees and their associated departments.
Tables: employees and departments.
Usually, joins are helpful when running reports from normalized and related tables.
This tutorial provided the concepts of working with JOIN statements in a PostgreSQL server. Use the samples in this tutorial to retrieve and display data from multiple tables in your database.