JIYIK CN >

The PostgreSQL official documentation states, ``LATERAL 关键字可以位于子SELECT FROM 项之前。这允许子SELECT 引用出现在FROM 中的FROM 项的列（如果没有LATERAL ，每个子 SELECT 都是独立评估的，因此不能交叉引用任何其他FROM 项目。）.

Left Join in PostgreSQL

Effectively, it is more like a for eachloop where you iterate over the results, and for each row, you perform a query (a subquery) on it. To demonstrate 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
);

Following is the query pastebinin insert. We created a producttable with 100 products and a table with 15 entries wishlist.

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 wish list. There are 15 wish list items and we have 100 products; it should return 5*15or 75rows after the query.

The pseudo code is as follows:

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

Now, let's write this in a PSQL query. If we use lateral, it can access each row and we can use ASto extract each row and assign that 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 --

This is what we do in our query:

1. Take a line off your wish list.
2. Checks if the price range of the row is less than or equal to the product.
3. Using in the query LIMIT 5, we limit 5 products for each row in the wish list.
4. ORDER BY PRODUCT.price_per_unit DESCOccupies the first few rows based on price.

The output is exactly what we want. It is more like a query with parameters join.

Left Join in PostgreSQL

To demonstrate this, we make another table for the purchase history. This table contains 1000 transactions for each user in the wish list above.

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

After inserting the data from here the table will look like this.

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 the first order, second order date, and second purchase item name of each user. Therefore, you need to use jointhe command to perform a subquery.

The SQL is as follows:

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;

This is 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 LATERAL, we can access the rows of a query. LEFT JOIN LATERALprovides Q1the ability to iterate over all queries for each row in .

To learn more about LATERALand JOIN, visit here .