如何查询一张表所有行及另一张表符合条件的单行？求Left Join替代方案

以下是几种能实现「保留表A全部行，同时关联表B中符合特定条件的唯一行」的SQL方案，适配不同数据库场景：

1. 关联子查询（适合仅需表B少数字段） #

如果只需要从表B获取少量字段，可以直接在SELECT语句中嵌入子查询，针对表A的每一行返回符合条件的唯一值：

SELECT 
    a.*,
    -- 取表B中对应a_id、符合条件的最新一行的column1
    (SELECT b.column1 
     FROM table_b b 
     WHERE b.a_id = a.id AND b.condition = 'xxx' 
     ORDER BY b.create_time DESC LIMIT 1) AS b_col1,
    -- 同理获取其他字段
    (SELECT b.column2 
     FROM table_b b 
     WHERE b.a_id = a.id AND b.condition = 'xxx' 
     ORDER BY b.create_time DESC LIMIT 1) AS b_col2
FROM table_a a;

2. 窗口函数+CTE关联（精准控制唯一行） #

用ROW_NUMBER()窗口函数给表B符合条件的行按分组（关联表A的字段）排序，取每组第一行后再和表A左关联，能避免Left Join可能出现的多行匹配问题：

WITH ranked_b AS (
    SELECT 
        *,
        -- 按a_id分组，按创建时间倒序打序号，每组第一行rn=1
        ROW_NUMBER() OVER (PARTITION BY a_id ORDER BY create_time DESC) AS rn
    FROM table_b
    WHERE condition = 'xxx' -- 先过滤符合条件的行
)
SELECT a.*, rb.*
FROM table_a a
LEFT JOIN ranked_b rb ON a.id = rb.a_id AND rb.rn = 1;

3. OUTER APPLY/LATERAL JOIN（逐行匹配子查询） #

部分数据库支持OUTER APPLY（SQL Server）或LATERAL JOIN（PostgreSQL、MySQL 8.0+），可以针对表A的每一行执行子查询，直接返回符合条件的唯一行，写法更简洁：

SQL Server 写法 #

SELECT a.*, b.*
FROM table_a a
OUTER APPLY (
    SELECT TOP 1 * 
    FROM table_b b 
    WHERE b.a_id = a.id AND b.condition = 'xxx' 
    ORDER BY create_time DESC
) b;

PostgreSQL/MySQL 8.0+ 写法 #

SELECT a.*, b.*
FROM table_a a
LEFT JOIN LATERAL (
    SELECT * 
    FROM table_b b 
    WHERE b.a_id = a.id AND b.condition = 'xxx' 
    ORDER BY create_time DESC LIMIT 1
) b ON true;

4. 聚合子查询关联（基于唯一标识取行） #

如果表B有唯一标识字段（比如id），可以通过聚合函数（MAX/MIN）找到符合条件的唯一行ID，再关联表B：

SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b 
    ON a.id = b.a_id 
    AND b.id = (
        SELECT MAX(id) 
        FROM table_b 
        WHERE a_id = a.id AND condition = 'xxx'
    );

这里利用MAX(id)锁定唯一行，同时左关联会自动保留表A中无对应B行的记录。

内容的提问来源于stack exchange，提问作者Hadi Momin