LATERAL 连接

概念概述

LATERAL 连接（LATERAL Joins）指的是：LATERAL 连接允许子查询引用 FROM 子句中前面行的列，把相关子查询转化为可产生多行的连接，常用于每组取前 N。

从更严格的定义看，LATERAL is a SQL construct that allows a subquery in the FROM clause to reference columns of preceding items, so it is evaluated per outer row and can return multiple rows. 该概念在工程实践中的价值，在于它能帮助团队用统一的语言描述一个具体的质量属性或设计约束，从而在评审与协作中减少歧义。

为什么需要它

It is used for top-N-per-group queries, table functions, and rewriting correlated scalar subqueries as joins. 它与 Upsert with INSERT … ON CONFLICT、The RETURNING Clause、GIN Indexes for Composite Values 等概念经常一起出现，用于解决相近的工程问题。理解它的适用场景，有助于在多种备选方案之间做出有依据的取舍，而不是凭直觉选择技术方案。

常见误解

• a lateral join is just a comma-separated cross join
• lateral joins are always faster than correlated subqueries

需要避免的反模式

• using lateral where a plain join would express the intent
• forgetting that a lateral join without a condition can multiply rows

实践要点

1. Because the subquery can see columns of the item to its left, a lateral join expresses top-N-per-group naturally, for example selecting the three most recent orders for each customer.
2. On engines without a lateral keyword the same effect comes from a correlated subquery, but the planner may evaluate it differently and can fail to stream results efficiently.
3. A lateral join with no restricting predicate degenerates into a cross join, so always pair it with an ORDER BY plus LIMIT or a WHERE clause inside the subquery.

参考来源

https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL

代码示例

-- PostgreSQL LATERAL：外层每行驱动内层子查询一次
SELECT
  c.id AS customer_id,
  c.email,
  latest.order_id,
  latest.total_cents,
  latest.placed_at
FROM customers c
LEFT JOIN LATERAL (
  SELECT o.id AS order_id, o.total_cents, o.placed_at
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.placed_at DESC
  LIMIT 3
) latest ON true
WHERE c.region = 'eu'
ORDER BY c.id, latest.placed_at DESC;

-- 对照窗口函数版本：全量开窗后过滤
SELECT customer_id, order_id, total_cents, placed_at
FROM (
  SELECT
    customer_id, id AS order_id, total_cents, placed_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY placed_at DESC
    ) AS rn
  FROM orders
) ranked
WHERE rn <= 3;

-- 支撑索引：内层子查询的 LIMIT 才能秒回
CREATE INDEX idx_orders_customer_time
  ON orders (customer_id, placed_at DESC);

import { pgTable, serial, text, integer, timestamp } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { eq, gte, desc, and, sql } from 'drizzle-orm';
import { Pool } from 'pg';

const customers = pgTable('customers', {
  id: serial('id').primaryKey(),
  email: text('email').notNull(),
});

const orders = pgTable('orders', {
  id: serial('id').primaryKey(),
  customerId: integer('customer_id').notNull(),
  total: integer('total').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool);

async function recentBigOrders(minTotal: number) {
  const rows = await db
    .select({
      orderId: orders.id,
      email: customers.email,
      total: orders.total,
    })
    .from(orders)
    .innerJoin(customers, eq(orders.customerId, customers.id))
    .where(and(gte(orders.total, minTotal)))
    .orderBy(desc(orders.createdAt))
    .limit(50);

  return rows;
}

const qualified = await recentBigOrders(1000);
console.log('rows typed:', qualified[0]?.email.length ?? 0);
await pool.end();