T-SQL 左连接不返回空列

2021-09-10 00:00:00 tsql sql-server

我在 T-SQL 中有 2 个表，一个有 10 条记录，一个有 11 条记录.

I have 2 tables in T-SQL one with 10 records and one with 11.

select tbl.unit, tbl2.unid 
from tbl1
left join tbl2 on tbl2.UNID = tbl1.UNIT
where tbl2.Status = 'Main'
group by unit, UNID

当 tbl1 有 11 条记录时，这只会返回 10 条记录.我期待缺失的记录显示一个值，如果 UNIT 但 UNID 为空，但不存在.

This only returns 10 records when tbl1 has 11 records. I was expecting the missing record to show a value if UNIT but null for UNID but is just is not there.

我不明白为什么会这样

推荐答案

那么为什么 LEFT JOIN 不显示联接左侧的所有记录.

So why would a LEFT JOIN not show all the records from a left side of the join.

是bug吗?

很可能不会.

让我们看一个简化的例子.

Lets look at a simplified example.

表A有3条记录.

ID    ColA
1     Foo
2     Bar
3     Buzz

TableB 有 2 条记录

TableB has 2 records

ID    ColB
4     Foo
5     Bar

ColA 上的 INNER JOINColB 将返回 2 条记录.
只有找到匹配项的那些.

An INNER JOIN on ColA & ColB would return 2 records.
Only those where a match is found.

SELECT ColA, ColB 
FROM TableA a
JOIN TableB b ON b.ColB = a.ColA

返回:

ColA    ColB
Foo     Foo
Bar     Bar

LEFT JOIN 将返回 3 条记录.
右侧带有 NULL 表示不匹配.

A LEFT JOIN would return 3 records.
With a NULL on the right side for the unmatched.

SELECT ColA, ColB 
FROM TableA a
LEFT JOIN TableB b ON b.ColB = a.ColA

返回:

ColA    ColB
Foo     Foo
Bar     Bar
Buzz    null

但是如果在右侧的 WHERE 子句中使用了条件会怎样?

But what happens if a criteria is used in the WHERE clause for the right side?

SELECT ColA, ColB 
FROM TableA a
LEFT JOIN TableB b ON b.ColB = a.ColA
WHERE b.ColB IN ('Foo', 'Bar', 'Buzz')

返回:

ColA    ColB
Foo     Foo
Bar     Bar

什么?嗡嗡声"在哪里?

What? Where's the 'Buzz'?

你能猜到为什么 LEFT JOIN 看起来像一个 INNER JOIN 吗?

Can you guess why that LEFT JOIN seems to behave like an INNER JOIN?

解决方案是将此类标准放在ON 子句中.

The solution is to put such criteria in the ON clause. 

SELECT ColA, ColB 
FROM TableA a
LEFT JOIN TableB b 
   ON b.ColB = a.ColA AND b.ColB IN ('Foo', 'Bar', 'Buzz')

或者把条件放在WHERE中，但也允许NULL.

Or do put the criteria in the WHERE, but also allow NULL.

SELECT ColA, ColB 
FROM TableA a
LEFT JOIN TableB b 
   ON b.ColB = a.ColA
WHERE (b.ColB IN ('Foo', 'Bar', 'Buzz') 
       OR b.ColB IS NULL)

返回:

ColA    ColB
Foo     Foo
Bar     Bar
Buzz    null

现在嗡嗡声又回来了.

相关文章