How to Left Join Lateral in PostgreSQL

Shihab Sikder Feb 02, 2024
  1. Left Join in PostgreSQL
  2. Left Lateral Join in PostgreSQL
How to Left Join Lateral in PostgreSQL

PostgreSQL official documentation states, The LATERAL keyword can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.).

Left Join in PostgreSQL

Efficiently, it’s more like a for each loop, where you can iterate over the result, and for each row, you can perform a query (sub-query) over that. To demonstrate the LATERAL JOIN, let’s create the following table.

create table product (
    id INT,
    product_name VARCHAR(50),
    price_per_unit DECIMAL(5,2)
);

create table wishlist (
    wish_list_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50),
    price_range INT
);

Here are the insert queries in the pastebin. We’ve created a product table with 100 products and a wishlist table with 15 entries.

postgres=# select * from wishlist limit 5;
 wish_list_id | first_name | last_name |         email          | price_range
--------------+------------+-----------+------------------------+-------------
            1 | Riannon    | Nuzzetti  | rnuzzetti0@wp.com      |          82
            2 | Caresse    | Onyon     | conyon1@reddit.com     |          75
            3 | Lexi       | Fyndon    | lfyndon2@google.com.au |          95
            4 | Cybil      | Rycraft   | crycraft3@oaic.gov.au  |          21
            5 | Cherry     | Greir     | cgreir4@boston.com     |          46
(5 rows)

postgres=# select * from product order by price_per_unit DESC LIMIT 10;
 id |         product_name         | price_per_unit
----+------------------------------+----------------
 67 | Wine - White, Gewurtzraminer |          98.87
  3 | Irish Cream - Baileys        |          95.24
 31 | Tuna - Fresh                 |          93.49
 65 | Bar Energy Chocchip          |          90.22
 60 | Ecolab - Medallion           |          89.54
 70 | Yogurt - French Vanilla      |          86.18
 42 | Shrimp - Baby, Cold Water    |          86.15
 26 | Tea - Black Currant          |          85.92
 64 | Scallop - St. Jaques         |          85.71
 61 | Red Currants                 |          85.66
(10 rows)

postgres=#

You want to know the TOP 5 products on each wishlist. There are 15 wishlist entries, and we have 100 products; it should return 5*15 or 75 rows after the query.

The pseudo-code is like the following:

for wish in wishlist
    found = 0
    for product in productLists(DESC order)
        if found<5
            return product row
        else
            break

Now, let’s write it in the PSQL query. If we use lateral, it gives access to each row, and we can extract each row using the AS and assign the row.

SELECT * FROM wishlist as wish,
LATERAL (
    SELECT * FROM PRODUCT
    WHERE PRODUCT.price_per_unit < wish.price_range
    ORDER BY PRODUCT.price_per_unit DESC
    LIMIT 5
) AS L
ORDER BY wish_list_id, price_per_unit DESC;

Output:

 wish_list_id | first_name | last_name  |              email              | price_range | id |          product_name           | price_per_unit
--------------+------------+------------+---------------------------------+-------------+----+---------------------------------+----------------
            1 | Riannon    | Nuzzetti   | rnuzzetti0@wp.com               |          82 | 79 | Cheese - Victor Et Berthold     |          81.79
            1 | Riannon    | Nuzzetti   | rnuzzetti0@wp.com               |          82 | 76 | Pastry - Key Limepoppy Seed Tea |          81.45
            1 | Riannon    | Nuzzetti   | rnuzzetti0@wp.com               |          82 | 32 | Juice - Ocean Spray Kiwi        |          81.42
            1 | Riannon    | Nuzzetti   | rnuzzetti0@wp.com               |          82 | 41 | Wine - Domaine Boyar Royal      |          81.42
            1 | Riannon    | Nuzzetti   | rnuzzetti0@wp.com               |          82 | 84 | Propel Sport Drink              |          78.59
            2 | Caresse    | Onyon      | conyon1@reddit.com              |          75 | 73 | Muffin - Mix - Creme Brule 15l  |          74.82
            2 | Caresse    | Onyon      | conyon1@reddit.com              |          75 | 48 | Schnappes - Peach, Walkers      |          74.49
            2 | Caresse    | Onyon      | conyon1@reddit.com              |          75 | 36 | Ice Cream - Strawberry          |          73.52
            2 | Caresse    | Onyon      | conyon1@reddit.com              |          75 | 45 | Shark - Loin                    |          73.39
            2 | Caresse    | Onyon      | conyon1@reddit.com              |          75 | 47 | Clam - Cherrystone              |          73.37
            3 | Lexi       | Fyndon     | lfyndon2@google.com.au          |          95 | 31 | Tuna - Fresh                    |          93.49
            3 | Lexi       | Fyndon     | lfyndon2@google.com.au          |          95 | 65 | Bar Energy Chocchip             |          90.22
            3 | Lexi       | Fyndon     | lfyndon2@google.com.au          |          95 | 60 | Ecolab - Medallion              |          89.54
            3 | Lexi       | Fyndon     | lfyndon2@google.com.au          |          95 | 70 | Yogurt - French Vanilla         |          86.18
            3 | Lexi       | Fyndon     | lfyndon2@google.com.au          |          95 | 42 | Shrimp - Baby, Cold Water       |          86.15
            4 | Cybil      | Rycraft    | crycraft3@oaic.gov.au           |          21 | 80 | Pea - Snow                      |          20.96
            4 | Cybil      | Rycraft    | crycraft3@oaic.gov.au           |          21 | 83 | Creme De Menthe Green           |          20.88
            4 | Cybil      | Rycraft    | crycraft3@oaic.gov.au           |          21 | 15 | Bread - Sticks, Thin, Plain     |          20.57
            4 | Cybil      | Rycraft    | crycraft3@oaic.gov.au           |          21 | 39 | Gatorade - Cool Blue Raspberry  |          19.36
-- More --

Here’s what we did in the query:

  1. Took a row from the wishlist.
  2. Checked the price range of that row if it’s less than or equal to products.
  3. Using the LIMIT 5 in the query, we limit 5 products for each row in the wishlist.
  4. ORDER BY PRODUCT.price_per_unit DESC is taking the top rows according to the price.

And the output is as we exactly wanted. It is more like a join query with parameters.

Left Lateral Join in PostgreSQL

To demonstrate this, we’ve made another table for purchase history. That table consists of 1000 transactions in the wishlist table above for each user.

create table Purchase (
    transaction_id INT NOT NULL,
    user_id INT,
    product_id INT,
    date DATE
);

The table will look like this after inserting the data from here.

postgres=# select * from purchase;
 transaction_id | user_id | product_id |    date
----------------+---------+------------+------------
              1 |       1 |         43 | 2013-10-21
              2 |       7 |         24 | 2017-10-04
              3 |      12 |         60 | 2011-12-29
              4 |      11 |         17 | 2015-01-07
              5 |      15 |         21 | 2019-09-14
              6 |       2 |         41 | 2013-07-23
              7 |      15 |         41 | 2013-08-22
              8 |       3 |         27 | 2013-09-18
              9 |      15 |         24 | 2010-01-11
             10 |      12 |          4 | 2011-01-20
             11 |       2 |         34 | 2020-12-05
-- More --

Now, you want to know each user’s first order, second-order date, and second purchase item name. So, you need sub-queries with the join command.

The SQL is like the following:

SELECT first_name,First_Order,Next_Order,product_name as next_product_name FROM
(SELECT Purchase.user_id, min(date) AS First_Order FROM Purchase GROUP BY user_id) Q1
LEFT JOIN LATERAL
(SELECT user_id,wishlist.first_name,product_name, date AS Next_Order FROM Purchase,wishlist,product
WHERE user_id = Q1.user_id
    and date>Q1.First_Order
    and user_id=wishlist.wish_list_id
    and product_id = product.id
ORDER BY date ASC
LIMIT 1
) Q2 ON true;

Here’s the output of the following query.

 first_name | first_order | next_order |        next_product_name
------------+-------------+------------+---------------------------------
 Cybil      | 2010-02-23  | 2010-03-09 | Gatorade - Cool Blue Raspberry
 Simonne    | 2010-04-27  | 2010-06-23 | Gatorade - Cool Blue Raspberry
 Lexi       | 2010-07-12  | 2010-08-12 | Artichoke - Fresh
 Evaleen    | 2010-04-27  | 2010-05-22 | Bread - Sticks, Thin, Plain
 Noell      | 2010-04-03  | 2010-05-01 | Jameson Irish Whiskey
 Joyce      | 2010-02-26  | 2010-03-15 | Pastry - Baked Scones - Mini
 Trixi      | 2010-01-09  | 2010-01-13 | Cheese - Brie, Cups 125g
 Riannon    | 2010-04-30  | 2010-07-07 | Wine - Cotes Du Rhone Parallele
 Cherry     | 2010-04-20  | 2011-01-20 | Cheese - Brie, Cups 125g
 Caresse    | 2010-10-05  | 2011-02-03 | Cheese - Brie, Cups 125g
 Andonis    | 2010-01-11  | 2011-04-28 | Yogurt - French Vanilla
 Stephannie | 2010-05-31  | 2010-07-07 | Shrimp - 16/20, Iqf, Shell On
 Linn       | 2010-02-09  | 2010-03-25 | Food Colouring - Pink
 Matilda    | 2010-01-01  | 2010-03-14 | Propel Sport Drink
 Jesse      | 2010-05-16  | 2010-07-05 | Fennel
(15 rows)


postgres=#

Using the LATERAL, we can access the query’s rows. The LEFT JOIN LATERAL gives the ability to iterate all the queries for each row in the Q1.

To know more about the LATERAL and JOIN, visit here.

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website