SQL CASE查询问题：笛卡尔积导致结果过多如何修正？

我有两个结构简单的SQL表：

• table_a：包含6条数据，字段为id、name、x_coord、y_coord
• table_b：包含8条数据，字段为id、name、x_coord、y_coord

需要编写SQL查询，返回table_a的所有name字段，同时新增一个status列：当table_a的某条记录与table_b中存在x_coord和y_coord均匹配的记录时，status值为'y'，否则为'n'。

但执行以下查询后返回了48条结果（6×8的笛卡尔积），不符合预期的6条结果，请问该如何修正？

select
    a.name,
    CASE WHEN (a.x_coord = b.x_coord and a.y_coord = b.y_coord) THEN 'y' ELSE 'n' END as status
from
    table_a a, table_b b

方案1：使用EXISTS子查询 #

通过EXISTS判断当前table_a的记录是否在table_b中有匹配的坐标，每条table_a记录仅返回一次：

select
    a.name,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM table_b b 
            WHERE b.x_coord = a.x_coord AND b.y_coord = a.y_coord
        ) THEN 'y' 
        ELSE 'n' 
    END as status
from
    table_a a

方案2：使用LEFT JOIN + 聚合函数 #

先通过LEFT JOIN关联两表的坐标，再用聚合函数判断是否存在匹配记录，最后按table_a的字段分组去重：

select
    a.name,
    CASE 
        WHEN MAX(CASE WHEN b.x_coord IS NOT NULL THEN 1 ELSE 0 END) = 1 THEN 'y' 
        ELSE 'n' 
    END as status
from
    table_a a
left join table_b b on a.x_coord = b.x_coord and a.y_coord = b.y_coord
group by
    a.id, a.name, a.x_coord, a.y_coord

关键说明 #

原查询使用了隐式交叉连接（table_a a, table_b b），会生成两表的笛卡尔积，因此得到6×8=48条结果。上述两种方案均能避免笛卡尔积，确保仅返回table_a的6条记录，同时正确标记每条记录的匹配状态。

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