FULL OUTER JOIN 值条件

Question

我需要向 FULL OUTER JOIN 添加值条件。

即我正在尝试这样做：

SELECT *
FROM Table1
FULL OUTER JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field2 > 5

但是这个脚本不起作用。实际上，看起来条件（Table2.Field2 > 5）根本没有被应用。

RIGHT OUTER JOIN 也会出现同样的问题，所以我认为原因是当存在 RIGHT 或 FULL join 时，没有值条件应用于连接中的右表。
为什么会发生这种情况？这种行为有概念性的解释吗？

当然，主要问题是如何解决这个问题。

有没有办法在不使用子查询的情况下解决这个问题？

SELECT *
FROM Table1
FULL OUTER JOIN (SELECT * FROM Table2 WHERE Table2.Field2 > 5) AS t2 ON Table1.Field1 = t2.Field1

Answer 1

您想要的可能会被重新表述为：

 SELECT *
   FROM Table1
   LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field2 > 5
  UNION ALL
 SELECT *
   FROM Table1
  RIGHT JOIN Table2 ON Table1.Field1 = Table2.Field1
  WHERE Table2.Field2 > 5
    AND Table1.Field1 IS NULL

但按照您自己的建议使用子查询是我认为最好的选择。

Answer 2

这曾经让我很困惑。现在我明白了！
“on”之后的条件（在您的情况下：Table1.Field1 = Table2.Field1 AND Table2.Field2 > 5 ）告诉连接运算符两个表中的哪些行被连接。这意味着当且仅当 table1 中的 row1 和表中的 row2 满足 row1.field1 = row2.field2 和 row2.field2 > 时。 5 row1 和 row2 已连接。其余行未连接。

因此，在完全外连接中，结果集将是表 1 中的连接行、非连接行和表 2 中的非连接行。
在右连接中，结果集将是 table2 中的连接行和非连接行。
无论哪种情况，表 2 中字段 2 <= 5 的行都将属于表 2 中非连接行的结果集。

这就是为什么“Table2.Field2 > 5”在左连接中可以正常工作，但在右连接或完全连接中却不能“正确”工作，但值条件确实可以正确完成其工作。

Answer 3

相当简洁，但没有子查询

SELECT  Table1.*
        , CASE WHEN Table2.Field2 > 5 THEN Table2.Field1 ELSE NULL END
        , CASE WHEN Table2.Field2 > 5 THEN Table2.Field2 ELSE NULL END        
FROM    Table1
        FULL OUTER JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE   COALESCE(Table2.Field2, 6) > 5
        OR Table1.Field1 = Table2.Field1

测试脚本

;WITH Table1 AS (
  SELECT * FROM (VALUES
    (1, 1)
    , (2, 2)
    , (5, 5)
    , (6, 6)
  ) AS Table1 (Field1, Field2)
)
, Table2 AS (
  SELECT * FROM (VALUES
    (1, 1)
    , (3, 3)
    , (4, 4)
    , (5, 5)
    , (7, 7)
  ) AS Table2 (Field1, Field2)
)
SELECT  Table1.*
        , CASE WHEN Table2.Field2 > 5 THEN Table2.Field1 ELSE NULL END
        , CASE WHEN Table2.Field2 > 5 THEN Table2.Field2 ELSE NULL END        
FROM    Table1
        FULL OUTER JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE   COALESCE(Table2.Field2, 6) > 5
        OR Table1.Field1 = Table2.Field1

结果

 Field1 Field2 Field1 Field2
 1      1      NULL   NULL
 5      5      NULL   NULL
 NULL   NULL   7      7
 6      6      NULL   NULL
 1      1      NULL   NULL
 2      2      NULL   NULL