SQL Server查询-如果不匹配，则返回空值 [英] SQL Server query - return null value if no match

问题描述

我遇到了由于某种原因无法解决的问题.我正在尝试编写一个连接两个表的查询，其中在一个表中可能找不到匹配项.如:

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')

如果Table2中没有匹配项，则不返回任何内容.但是，如果没有匹配项，我需要它只为该列返回NULL，并且仍然从Table1返回值.

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.

我已将所有想得到的一切都替换为JOIN，但无济于事.

I have changed up the JOIN with everything that I can think of, but to no avail.

Table2.Category可以包含多个其他值，因此执行OR IS NULL类型的交易将不起作用.

Table2.Category can contain multiple other values, so doing a OR IS NULL type of deal won't work.

因此，如果Table2.Category = 'Foo'没有匹配项，我仍然需要它返回:

So, if there is no match for Table2.Category = 'Foo', I am still needing it to return:

Table1 | Table2
----------------
 12345 |  NULL

有什么建议吗?

推荐答案

将table2的条件移出WHERE子句，移至JOIN.

Move the condition for table2 out of your WHERE clause and into your 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'

这篇关于SQL Server查询-如果不匹配，则返回空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！