How to Work With PostgreSQL Joins

Introduction

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.

Prerequisites

To proceed with this tutorial:

• Deploy an Ubuntu 20.04 server.

• Create a non-root sudo user.

• Install and configure a PostgreSQL server.

1. Set Up a Sample Database, a Few Tables, and Some Records

To better understand how PostgreSQL joins work, create a sample database, add a few tables, and load data by following the steps below:

1. Log in to the PostgreSQL server as user postgres.

    $ sudo -u postgres psql

2. 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

3. Connect to the new online_shop database:

    postgres=# \connect online_shop;

   Output.

    You are now connected to database "online_shop" as user "postgres".

4. 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

5. 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

6. 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)

7. 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

8. 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

9. 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.

2. Types of PostgreSQL Joins

This tutorial discusses the following basic types of PostgreSQL joins:

1. Left join.

2. Right join.

3. 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.

2.1. PostgreSQL Left Join

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) 

2.2. PostgreSQL Right Join

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)

2.3. PostgreSQL Inner Join

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)

3. PostgreSQL Join Use-cases

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.

Conclusion

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.