sql - INNER JOIN 之前的 UNION

Question

为什么当我像这样在 INNER JOIN 之前使用 UNION

    select some_table1.some_column1,
           some_table1.some_column2
    FROM some_table1
    UNION ALL
    select some_table2.some_column1,
           some_table2.some_column2
    FROM some_table2
    INNER JOIN  some_table3
    ON  some_table3.some_column1 = some_table1.some_column1 

我有一个错误:无法绑定(bind)多部分标识符“some_table3.some_column1”？
所有数据类型都兼容。

Answer 1

您收到错误，因为那不是有效的 SQL 语法。 UNION ALL语法是:

SELECT <column1>
FROM <table1>
UNION ALL
SELECT <column1>
FROM <table2>

您不能像您尝试做的那样引用任一查询中的列。如果你想引用，那么你会想要使用这样的东西:

select *
from 
(
    select some_table1.some_column1, some_table1.some_column2
    FROM some_table1
    UNION ALL
    select some_table2.some_column1, some_table2.some_column2
    FROM some_table2
) t1
INNER JOIN some_table3
ON  some_table3.some_column1 = t1.some_column1