SQL 返回包括 NULL 在内的所有值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15875900/
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
Return all values including NULL
提问by Farkiba
I have two tables in SQL Server 2008, and by doing a JOINI want to get all values, however I get only the values where records exist even though I need the fields with NULL records.
我在 SQL Server 2008 中有两个表，通过执行 aJOIN我想获取所有值，但是即使我需要带有 NULL 记录的字段，我也只获取记录存在的值。
Here is the example on how tableAlooks like
这是关于tableA外观的示例
|IPAddress |DNSRecord|
|192.168.1.1|Test |
|192.168.0.1|Test1 |
tableBstores the following records
tableB存储以下记录
|NetworkAddress|SerialNo |
|192.168.1.1 |1Z5A789DS|
|192.168.0.1 |NULL |
My query to return the fields I need is the following
我的查询返回我需要的字段如下
SELECT
t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND SerialNo LIKE '%' +@SerialNo +'%'
The problem with this query is that I get the following result
这个查询的问题是我得到以下结果
|IPAddress |DNSRecord|SerialNo |
|192.168.1.1|Test |1Z5A789DS|
And I would like to get returned the following result instead
我想返回以下结果
|IPAddress |DNSRecord|SerialNo |
|192.168.1.1|Test |1Z5A789DS|
|192.168.0.1|Test1 |NULL |
回答by bAN
Just add a condition for the case of SerialNo is NULL. With your actual condition, this case is rejected from selection
只需为 SerialNo 为 NULL 的情况添加一个条件。以您的实际情况，本案例拒绝入选
SELECT t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND ( SerialNo LIKE '%' +@SerialNo +'%' OR SerialNo is NULL)
回答by Farkiba
One alternative:
一种选择：
SELECT t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND coalesce(SerialNo, @SerialNo) LIKE '%' +@SerialNo +'%'
回答by d'alar'cop
Try using this instead:
尝试改用这个：
SELECT t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
FULL OUTER JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND SerialNo LIKE '%' +@SerialNo +'%'
See: OUTER JOIN
请参阅：OUTER JOIN
Cheers.
干杯。
回答by Harshil
Try this
尝试这个
SELECT t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND (SerialNo LIKE '%' +@SerialNo +'%' OR SerialNo IS NULL)
回答by Mihai Cristian
U can try this, i always use this INCLUDE_NULL_VALUESin my query or procedure, when i need the null values
你可以试试这个，INCLUDE_NULL_VALUES当我需要空值时，我总是在我的查询或过程中使用它
SELECT
t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND SerialNo LIKE '%' +@SerialNo +'%'
INCLUDE_NULL_VALUES
