理解PostgreSQL中的LATERAL__LATERAL是PostgreSQL和其他关系数据库(如Oracle、DB2和MS SQL)不太为⼈所知的特性之⼀。然⽽， LATERAL是⼀个⾮常有⽤的特性，看看使⽤它们可以实现什么。: 世界上功能最强大的开源数据库...

### **作者**

#### Hans-Jürgen Schönig
### **译者**

#### 张文升
#### LATERAL是PostgreSQL和其他关系数据库(如Oracle、DB2和MS SQL)不太为⼈所知的特性之⼀。然⽽， LATERAL是⼀个⾮常有⽤的特性，看看使⽤它们可以实现什么。

### **深究 FROM**

#### 在我们深⼊研究LATERAL之前，有必要坐下来从更哲学的层⾯考虑 SQL 中的 SELECT 和 FROM ⼦句。下⾯ 是⼀个例⼦：
```
SELECT whatever FROM tab;
```

#### 基本上，我们可以将此语句视为⼀个循环。⽤伪代码编写此 SQL 语句类似于以下代码段：
```
for x in tab
loop
 “do whatever”
end loop
```
#### 对于表中的每个条⽬，执⾏SELECT⼦句的操作。通常数据只是按原样返回。⼀个SELECT语句可以看作是⼀ 个循环。但是如果我们需要⼀个“嵌套”循环呢?这正是LATERAL的优势所在。

### **LATERAL joins : 创建示例数据**

#### 让我们想象⼀个简单的例⼦。假设我们有⼀系列的产品，我们也有客户的愿望清单。现在的⽬标是为每个愿 望清单找到最好的3个产品。下⾯的SQL⽚段创建了⼀些示例数据:
```
CREATE TABLE t_product AS
 SELECT id AS product_id,
 id * 10 * random() AS price,
 'product ' || id AS product
 FROM generate_series(1, 1000) AS id;
CREATE TABLE t_wishlist
(
 wishlist_id int,
 username text,
 desired_price numeric
);
INSERT INTO t_wishlist VALUES
(1, 'hans', '450'),
 (2, 'joe', '60'),
 (3, 'jane', '1500');
```
#### 产品表中填充了1000个产品。价格是随机的，我们⽤了⼀个很有创意的名字来命名产品:
```
test=# SELECT * FROM t_product LIMIT 10;
 product_id | price | product
------------+--------------------+------------
 1 | 6.756567642432323 | product 1
 2 | 5.284467408540081 | product 2
 3 | 28.284196164210904 | product 3
 4 | 13.543868035690423 | product 4
 5 | 30.576923884383156 | product 5
 6 | 26.572431211361902 | product 6
 7 | 64.84599396020204 | product 7
 8 | 21.550701384168747 | product 8
 9 | 28.995584553969174 | product 9
 10 | 17.31335004787411 | product 10
(10 rows)
```
#### 接下来，我们有⼀个愿望清单。
```
test=# SELECT * FROM t_wishlist;
 wishlist_id | username | desired_price
-------------+----------+---------------
 1 | hans | 450
 2 | joe | 60
 3 | jane | 1500
(3 rows)
```
#### 如您所⻅，愿望清单属于⼀个⽤户，我们想要推荐的这三种产品都有⼀个期望的价格。
#### Running LATERAL joins After providing some sample data and loading it into our PostgreSQL database, we can approach the problem and try to come up with a solution.
#### Suppose we wanted to find the top three products for every wish, in pseudo-code:

### **运⾏ LATERAL joins** 
#### 在提供⼀些示例数据并将其加载到PostgreSQL数据库之后，我们就可以着⼿解决这个问题并尝试找到解决⽅案。
#### 假设我们想要在伪代码中找到每个愿望的前三名产品:
```
for x in wishlist
loop
for y in products order by price desc
 loop
 found++
 if found <= 3
 then
 return row
 else
 jump to next wish
 end
 end loop
end loop
```
#### 重要的是我们需要两个循环。⾸先，我们需要遍历愿望清单，然后查看已排序的产品列表，选择3个并继续下 ⼀个愿望清单。
#### 让我们看看如何使⽤LATERAL-join来实现这⼀点:
```
SELECT *
FROM t_wishlist AS w,
 LATERAL (SELECT *
 FROM t_product AS p
 WHERE p.price < w.desired_price
 ORDER BY p.price DESC
 LIMIT 3
 ) AS x
ORDER BY wishlist_id, price DESC;
```
#### 我们将⼀步⼀步地完成它。您在FROM⼦句中⾸先看到的是t_wishlist表。LATERAL现在所能做的就是使 ⽤愿望清单中的条⽬来发挥它的魔⼒。因此，对于愿望清单中的每个条⽬，我们都选择了三个产品。为了弄 清楚我们需要哪些产品，我们可以使⽤w.desired_price。换句话说：它就像⼀个“带参数的连接”。FROM ⼦句是我们伪代码中的“外部循环”，⽽LATERAL可以看作是“内部循环”。
#### 结果集如下所示：
```
  wishlist_id | username | desired_price | product_id | price
| product
-------------+----------+---------------+------------+--------------------
+-------------
 1 | hans | 450 | 708 | 447.0511375753179
| product 708
 1 | hans | 450 | 126 | 443.6560873146138
| product 126
 1 | hans | 450 | 655 | 438.0566432022443
| product 655
 2 | joe | 60 | 40 | 59.32252841190291
| product 40
 2 | joe | 60 | 19 | 59.2142714048882
| product 19
 2 | joe | 60 | 87 | 58.78014573804254 
| product 87
 3 | jane | 1500 | 687 | 1495.8794483743645
| product 687
 3 | jane | 1500 | 297 | 1494.4586352980593
| product 297
 3 | jane | 1500 | 520 | 1490.7849437550085
| product 520
(9 rows)
```
#### PostgreSQL为每个愿望清单返回3个条⽬，这正是我们想要的。这⾥的重要部分是，LIMIT⼦句位于 LATERAL的SELECT内部。因此，它限制了每个愿望清单的⾏数，⽽不是总⾏数。
#### PostgreSQL在优化LATERAL连接⽅⾯做得⾮常好。在我们的例⼦中，执⾏计划看起来⾮常简单:
```
test=# explain SELECT *
FROM t_wishlist AS w,
 LATERAL (SELECT *
 FROM t_product AS p
 WHERE p.price < w.desired_price
 ORDER BY p.price DESC
 LIMIT 3
 ) AS x
ORDER BY wishlist_id, price DESC;
 QUERY PLAN
--------------------------------------------------------------------------
-------------
 Sort (cost=23428.53..23434.90 rows=2550 width=91)
 Sort Key: w.wishlist_id, p.price DESC
 -> Nested Loop (cost=27.30..23284.24 rows=2550 width=91)
 -> Seq Scan on t_wishlist w (cost=0.00..18.50 rows=850 width=68)
 -> Limit (cost=27.30..27.31 rows=3 width=23)
 -> Sort (cost=27.30..28.14 rows=333 width=23)
 Sort Key: p.price DESC
 -> Seq Scan on t_product p (cost=0.00..23.00 rows=333
width=23)
 Filter: (price < (w.desired_price)::double
precision)
(9 rows)
```
#### LATERAL连接⾮常有⽤，在很多情况下都可以⽤来加速操作，或者使代码更容易理解。

### **写在最后** 
#### 如果你想了解更多关于join的⼀般知识，如果你想阅读更多关于PostgreSQL的内容，现在考虑看看Laurenz Albe关于PostgreSQL的join策略的优秀帖⼦。

#### 如果你想了解更多关于PostgreSQL优化器的⼀般知识，如果你想了解更多关于优化和其他与PostgreSQL查 询优化相关的重要主题，请查看我关于优化器的博客⽂章。

### **原文链接：<https://www.cybertec-postgresql.com/en/understanding-lateral-joins-in-postgresql/>**

#### **PostgreSQL中文社区欢迎广大技术人员投稿**
#### **投稿邮箱：press@postgres.cn**