Oracle中多Lateral Join/Apply语句的正确语法求解

云上老周

2026-7-19

Oracle 中 Lateral Join/APPLY 的正确实现语法

基于需求（关联authors和books表，通过Lateral Join计算税费、含税总价并获取作者信息），以下是符合Oracle语法规范的两种实现方式：

方式一：使用`LATERAL`关键字

SELECT
    b.id, b.title,
    b.price, sq.tax, sq2.inc,
    a.givenname, a.othernames, a.familyname,
    a.home
FROM books b
    CROSS JOIN LATERAL (SELECT b.price * 0.1 AS tax FROM dual) sq
    CROSS JOIN LATERAL (SELECT b.price + sq.tax AS inc FROM dual) sq2
    LEFT JOIN LATERAL (SELECT givenname, othernames, familyname, home 
                       FROM authors 
                       WHERE id = b.authorid) a ON true;

关键语法说明：

Oracle要求单行计算的子查询必须包含FROM dual
子查询内必须通过AS为计算列定义别名（如AS tax）
表别名不能使用AS，直接指定别名即可（如sq、a）
用CROSS JOIN LATERAL实现无条件关联，用LEFT JOIN LATERAL ... ON true实现左关联

方式二：使用`CROSS APPLY`/`OUTER APPLY`

SELECT
    b.id, b.title,
    b.price, sq.tax, sq2.inc,
    a.givenname, a.othernames, a.familyname,
    a.home
FROM books b
    CROSS APPLY (SELECT b.price * 0.1 AS tax FROM dual) sq
    CROSS APPLY (SELECT b.price + sq.tax AS inc FROM dual) sq2
    OUTER APPLY (SELECT givenname, othernames, familyname, home 
                 FROM authors 
                 WHERE id = b.authorid) a;