UNION合并T1、T2后JOIN T3报错‘UNEXPECTED JOIN’求助

错误原因 #

1. UNION结果未作为独立数据集处理：SQL语法规则中，UNION合并后的结果必须被包裹为子查询或CTE，才能与其他数据集执行JOIN操作，直接在UNION后追加JOIN会导致语法解析失败，触发"UNEXPECTED JOIN"错误。
2. T3子查询语法错误：WHERE子句位置错误，必须放在FROM T3之后才能生效。
3. 缺少JOIN关联条件：未指定两张数据集的匹配字段，会产生无意义的笛卡尔积，不符合业务需求（需通过SCHOOL和TEACHER关联）。

修正后的SQL语句 #

方式1：子查询包裹UNION结果

SELECT *
FROM (
    SELECT SCHOOL, STUDENT, TEACHER FROM T1
    UNION
    SELECT SCHOOL, STUDENT, TEACHER FROM T2
) AS T1_T2_UNION
JOIN (
    SELECT SCHOOL, TEACHER, ID
    FROM T3
    WHERE SCHOOL != 10
) AS T3_FILTERED
ON T1_T2_UNION.SCHOOL = T3_FILTERED.SCHOOL 
AND T1_T2_UNION.TEACHER = T3_FILTERED.TEACHER;

方式2：CTE（公共表表达式）写法更清晰

WITH T1_T2_UNION AS (
    SELECT SCHOOL, STUDENT, TEACHER FROM T1
    UNION
    SELECT SCHOOL, STUDENT, TEACHER FROM T2
), T3_FILTERED AS (
    SELECT SCHOOL, TEACHER, ID
    FROM T3
    WHERE SCHOOL != 10
)
SELECT *
FROM T1_T2_UNION
JOIN T3_FILTERED
ON T1_T2_UNION.SCHOOL = T3_FILTERED.SCHOOL 
AND T1_T2_UNION.TEACHER = T3_FILTERED.TEACHER;

补充说明 #

• 如果不需要自动去重（允许保留T1、T2中的重复行），可以将UNION替换为UNION ALL，提升查询性能。
• 关联条件ON指定的SCHOOL和TEACHER字段，确保查询结果与目标表T4的结构和数据一致。

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