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:
- Took a row from the wishlist.
- Checked the price range of that row if it’s less than or equal to products.
- Using the
LIMIT 5
in the query, we limit 5 products for each row in the wishlist. 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.