主页 / dba / 问题 / 115312
Accepted
blobbles
Asked: 2015-09-17 18:54:48 +0800 CST

具有连接条件的 OUTER JOIN 会导致意外结果

  • 772

SQL Server 2008 R2，但在所有其他版本的 SQL Server 上也会发现可能的行为。

这可能看起来很明显，但对我来说这似乎是一个错误。

以下查询给出了意外的结果，这里是设置：

CREATE TABLE #Base (
Key1 int,
RefDate date
)


CREATE TABLE #JoinTable (
    Key1 int,
    RefDate date
    )

INSERT INTO #Base
SELECT 1, '2012-05-05'
UNION
SELECT 2, '2013-06-06'
UNION
SELECT 3, '2014-07-07'
UNION
SELECT 4, '2015-08-08'
UNION
SELECT 5, '2016-09-09'


INSERT INTO #JoinTable
SELECT 4, '2012-05-05'
UNION
SELECT 5, '2013-06-06'
UNION
SELECT 6, '2014-07-07'
UNION
SELECT 7, '2015-08-08'
UNION
SELECT 8, '2016-09-09'

以下查询按我的预期执行，返回仅出现在基表中的 3 行：

SELECT * 
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1
WHERE j.Key1 IS NULL

现在我想将行限制为 2014 年 1 月 1 日之前的行。据我了解，我可以采用以下两种方法，两者都应该是完全可行的：

SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 AND b.RefDate < '2014-01-01'
WHERE j.Key1 IS NULL


SELECT b.* 
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 
WHERE j.Key1 IS NULL AND b.RefDate < '2014-01-01'

但是，只有第二个查询返回我想要的数据。第一个似乎忽略了连接行中 AND 之后添加的连接条件。

为什么？

（sql小提琴链接在这里）

sql-server join

2 个回答

  1. Best Answer

    这不是错误，它是如何LEFT JOIN工作的。

    b LEFT JOIN j ON <SomeCondition>

    b无论连接条件如何，都会返回所有行。连接条件仅定义/限制将从中返回哪些行j。

    SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 AND b.RefDate < '2014-01-01'
WHERE j.Key1 IS NULL

    你可以这样想：

    1. 遍历 的所有行#Base。
    2. 对于中的每一行，#Base查找#JoinTable满足连接条件 ( b.Key1 = j.Key1 AND b.RefDate < '2014-01-01') 的所有行。中可能没有这样的行#JoinTable，在本例中为 return NULLs。请注意，连接条件不过滤#Base，它只过滤#JoinTable。

    当你投入时b.RefDate < '2014-01-01'，WHERE你就是在过滤#Base。

    • 6

  2. 第一个查询连接被读取为“join table #jointable only if #Base.RefDate < '2014-01-01'”，除了来自在#JoinTable 中没有相关行的#Base。

    第二个查询读作“返回#Base dated < '2014-01-01' 中在#JoinTable 中没有相关行的所有行”。

    我猜你想用 left join 替换一个不存在的查询.. where is null. 您应该检查原始查询，其中一个条件是：

    ... and not exists (select null from #JoinTable j where j.Key = b.Key and b.RefDate < '2014-01-01') 对于第一个查询

    或... and not exists (select null from #JoinTable j where j.Key = b.Key) and b.RefDate < '2014-01-01'第二个查询。

    总之，这不是一个错误。

    HTH

    • 0

相关问题