問題描述

我在 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

現在嗡嗡聲又回來了.

這篇關于T-SQL 左連接不返回空列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！