主页 / dba / 问题 / 333909
Accepted
Nikita Glukhov
Asked: 2023-12-10 13:30:58 +0800 CST

Postgres LATERAL JOIN 的 ON 谓词

  • 772

Postgres LATERAL JOIN 的 ON 谓词如何工作？

让我澄清一下问题。我已经阅读了官方文档和一堆关于这种 JOIN 的文章。据我了解，它是一个带有相关子查询的 foreach 循环 - 它迭代表 A 的所有记录，允许引用相关子查询 B 中“当前”行的列并将 B 的结果集连接到A 的“当前”行 - 如果 B 查询返回 1 行，则只有一对，如果 B 查询返回 N 行，则有 N 对与 A 的重复“当前”行。与通常的 JOIN 中的行为相同。

但为什么需要 ON 谓词呢？对我来说，在通常的 JOIN 中，我们使用 ON ，因为我们有 2 个表的笛卡尔积需要过滤掉，而 LATERAL JOIN 的情况则不同，后者直接生成结果对。换句话说，在我的开发经验中，我只见过 CROSS JOIN LATERAL 和 LEFT JOIN LATERAL () ON TRUE （不过后者看起来相当笨拙），但有一天，一位同事向我展示了

SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
    SELECT rts.route_id, array_agg(rts.shipment_id) shipment_ids
    FROM route_to_shipment rts
    where rts.route_id = r.route_id
    GROUP BY rts.route_id
) rts using (route_id)

这让我大吃一惊。为什么using (route_id)？我们已经有了where rts.route_id = r.route_id子查询！也许我对横向连接机制的理解错误？

postgresql

2 个回答

  1. CREATE TABLE ta (aid INT, a INT);
CREATE TABLE tb (aid INT, b INT);
INSERT INTO ta VALUES (1,10),(2,20);
INSERT INTO tb VALUES (1,100),(1,200);
SELECT * FROM ta LEFT JOIN LATERAL (SELECT * FROM tb WHERE tb.aid=ta.aid) ON true;

 aid | a  | aid  |  b
-----+----+------+------
   1 | 10 |    1 |  100
   1 | 10 |    1 |  200
   2 | 20 | Null | Null

SELECT * FROM ta LEFT JOIN LATERAL (SELECT * FROM tb) USING (aid);

 aid | a  |  b
-----+----+------
   1 | 10 |  100
   1 | 10 |  200
   2 | 20 | Null

    USING (columns) 子句不会复制结果集中的指定列，而 ON (ta.column=tb.column) 会复制列。这里重复的列是“援助”。在标准 JOIN 相等的情况下，列将相等，因此重复是无用的，这意味着 USING 更可取。它也更具可读性。在外部 JOIN（右、左、全）的情况下，您可能希望复制两列，以便知道其中一列是否为 NULL。

    如果您想要 CROSS JOIN（无 ON 条件）：

    SELECT * FROM ta CROSS JOIN LATERAL (SELECT * FROM tb WHERE tb.aid=ta.aid);

    您还可以使用 JOIN 并将 LATERAL 表的 WHERE 中的一些条件移动到 ON() 子句中，结果是相同的：

    SELECT * FROM ta JOIN LATERAL (SELECT * FROM tb WHERE ...) ON (tb.aid=ta.aid);

    但是没有 CROSS LEFT JOIN，因此如果您想要 LEFT JOIN LATERAL，则必须显式声明 LEFT JOIN，这需要 ON 子句。

    SELECT * FROM ta JOIN LATERAL (SELECT * FROM tb WHERE tb.aid=ta.aid) ON true WHERE ta.aid<10;

    事实上，在 LATERAL 连接的情况下，ON 子句可能是多余的。

    • 1
  2. Best Answer

    简短的回答：LEFT JOIN 需要一个连接条件 - 而不是CROSS JOIN. 手册中的基础知识。
    也可以看看：

    但是，在计算横向子查询中的集合后，连接条件仍然可以过滤要附加到右侧的行。喜欢：

    SELECT r.acceptance_status
     , count(*) AS count_routes
     , count(rts.shipment_ids) AS count_routes_with_more_than_one_shipment
FROM   route r
LEFT   JOIN LATERAL (
   SELECT array_agg(rts.shipment_id) shipment_ids
        , count(*) AS shipments
   FROM   route_to_shipment rts
   WHERE  rts.route_id = r.route_id
   -- GROUP  BY rts.route_id  -- just noise
   ) rts ON shipments > 1;  -- !!!

    这将返回table 中的所有行route，但仅附加在 table 中找到shipment_ids多个相关行的地方。route_to_shipment

    无需添加到子查询列表rts.route_id中。之后只是噪音。 我仍然徒劳地生成数组，就像你原来的那样。SELECT
    GROUP BY rts.route_idWHERE rts.route_id = r.route_id
    shipment_ids

    还展示了count(*)与 的不同结果count(shipment_ids)。

    连接条件不能移动到WHERE子句，否则会产生不同的效果。不过，您可以HAVING向 suquery 添加一个子句：

    SELECT r.acceptance_status
     , count(*) AS ct_routes
     , count(rts.shipment_ids) AS ct_routes_with_more_than_1_shipment
FROM   route r
LEFT   JOIN LATERAL (
   SELECT array_agg(rts.shipment_id) shipment_ids
   FROM   route_to_shipment rts
   WHERE  rts.route_id = r.route_id
   HAVING count(*) > 1  -- !!!
   ) rts ON true
GROUP  BY r.acceptance_status;

    但是存在没有聚合的横向子查询（因此不可能HAVING有子句）。对于你的情况：

    SELECT r.acceptance_status
     , count(*) AS ct_routes
     , count(rts.shipment_ids) AS ct_routes_with_more_than_1_shipment
FROM   route r
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT rts.shipment_id
      FROM   route_to_shipment rts
      WHERE  rts.route_id = r.route_id
      ) AS shipment_ids
   ) rts ON cardinality(shipment_ids) > 1  -- !!!
GROUP  BY r.acceptance_status;

    小提琴

    当然，只有当我们要使用该数组时才有意义。那么，数组构造函数可能是最适合您的查询的。看：

    • 1

相关问题