SQL Server 查询 - 如果不匹配则返回空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14160958/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server query - return null value if no match
提问by BrewingDev
I'm running into a problem that I cannot figure out for some reason. I am trying to write a query that joins two tables, in which a match may not be found in a table. Such as:
我遇到了一个由于某种原因无法解决的问题。我正在尝试编写一个连接两个表的查询，其中一个表中可能找不到匹配项。如：
SELECT
Table1.IDField, Table2.IDField
FROM
Table1
LEFT OUTER JOIN
Table2 ON Table1.PersonID = Table2.PersonID
WHERE
(Table1.IDField = '12345')
AND (Table2.Category = 'Foo')
If there is no match in Table2, it's not returning anything. However, I need it to just return a NULL for that column if there is no match and still return the value from Table1.
如果 中没有匹配项Table2，则不会返回任何内容。但是，如果没有匹配项，我需要它只为该列返回一个 NULL 并且仍然从Table1.
I have changed up the JOINwith everything that I can think of, but to no avail.
我已经JOIN用我能想到的一切改变了，但无济于事。
Table2.Categorycan contain multiple other values, so doing a OR IS NULLtype of deal won't work.
Table2.Category可以包含多个其他值，因此进行某种OR IS NULL交易是行不通的。
So, if there is no match for Table2.Category = 'Foo', I am still needing it to return:
所以，如果没有匹配的Table2.Category = 'Foo'，我仍然需要它返回：
Table1 | Table2
----------------
12345 | NULL
Any suggestions?
有什么建议？
回答by Michael Fredrickson
Move the condition for table2out of your WHEREclause and into your JOIN.
将条件 fortable2移出您的WHERE子句并移入您的JOIN.
SELECT
Table1.IDField, Table2.IDField
FROM
Table1
LEFT OUTER JOIN Table2
ON Table1.PersonID = Table2.PersonID
AND Table2.Category = 'Foo'
WHERE
Table1.IDField = '12345'
回答by Melanie
Try this:
尝试这个：
LEFT OUTER JOIN
Table2 ON Table1.PesonID = Table2.PersonID
AND Table2.Category = 'Foo'
then delete the 'Foo' line from the WHERE clause
然后从 WHERE 子句中删除 'Foo' 行
回答by bejaysea
The problem isn't in the join per se, but in the requirement in the where clause that match table2. I've solved this with a where coalese(table2.category, 'Foo') = 'Foo'. That way if the table2 is null, it will still match.
问题不在于连接本身，而在于匹配 table2 的 where 子句中的要求。我已经用 where Coalese(table2.category, 'Foo') = 'Foo' 解决了这个问题。这样，如果 table2 为空，它仍然会匹配。
