如何让 SQL INNER JOIN 接受空结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7588612/
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
How to have SQL INNER JOIN accept null results
提问by Tom Redman
I have the following query:
我有以下查询：
SELECT TOP 25 CLIENT_ID_MD5, COUNT(CLIENT_ID_MD5) TOTAL
FROM dbo.amazonlogs
GROUP BY CLIENT_ID_MD5
ORDER BY COUNT(*) DESC;
Which returns:
返回：
283fe255cbc25c804eb0c05f84ee5d52 864458
879100cf8aa8b993a8c53f0137a3a176 126122
06c181de7f35ee039fec84579e82883d 88719
69ffb6c6fd5f52de0d5535ce56286671 68863
703441aa63c0ac1f39fe9e4a4cc8239a 47434
3fd023e7b2047e78c6742e2fc5b66fce 45350
a8b72ca65ba2440e8e4028a832ec2160 39524
...
I want to retrieve the corresponding client name (FIRM) using the returned MD5 from this query, so a row might look like:
我想使用此查询返回的 MD5 检索相应的客户端名称 (FIRM)，因此一行可能如下所示：
879100cf8aa8b993a8c53f0137a3a176 126122 Burger King
So I made this query:
所以我做了这个查询：
SELECT a.CLIENT_ID_MD5, COUNT(a.CLIENT_ID_MD5) TOTAL, c.FIRM
FROM dbo.amazonlogs a
INNER JOIN dbo.customers c
ON c.CLIENT_ID_MD5 = a.CLIENT_ID_MD5
GROUP BY a.CLIENT_ID_MD5, c.FIRM
ORDER BY COUNT(*) DESC;
This returns something like:
这将返回如下内容：
879100cf8aa8b993a8c53f0137a3a176 126122 Burger King
06c181de7f35ee039fec84579e82883d 88719 McDonalds
703441aa63c0ac1f39fe9e4a4cc8239a 47434 Wendy's
3fd023e7b2047e78c6742e2fc5b66fce 45350 Tim Horton's
Which works, except I need to return an empty value for c.FIRM if there is no corresponding FIRM for a given MD5. For example:
哪个有效，除非我需要为 c.FIRM 返回一个空值，如果给定的 MD5 没有相应的 FIRM。例如：
879100cf8aa8b993a8c53f0137a3a176 126122 Burger King
06c181de7f35ee039fec84579e82883d 88719 McDonalds
69ffb6c6fd5f52de0d5535ce56286671 68863
703441aa63c0ac1f39fe9e4a4cc8239a 47434 Wendy's
3fd023e7b2047e78c6742e2fc5b66fce 45350 Tim Horton's
How should I modify the query to still return a row even if there is no corresponding c.FIRM?
即使没有相应的 c.FIRM，我应该如何修改查询以仍然返回一行？
回答by evilone
Replace INNER JOINwith LEFT JOIN
替换INNER JOIN为LEFT JOIN
回答by Punit
use LEFT JOINinstead of INNER JOIN
使用LEFT JOIN代替INNER JOIN
回答by Ian Nelson
Instead of doing an INNER join, you should do a LEFT OUTER join:
而不是做一个内部连接，你应该做一个左外连接：
SELECT
a.CLIENT_ID_MD5,
COUNT(a.CLIENT_ID_MD5) TOTAL,
ISNULL(c.FIRM,'')
FROM
dbo.amazonlogs a LEFT OUTER JOIN
dbo.customers c ON c.CLIENT_ID_MD5 = a.CLIENT_ID_MD5
GROUP BY
a.CLIENT_ID_MD5,
c.FIRM
ORDER BY COUNT(0) DESC
回答by Joe
An inner join excludes NULLs; you want a LEFT OUTER join.
内连接排除 NULL；你想要一个 LEFT OUTER 加入。
回答by NotMe
SELECT a.CLIENT_ID_MD5, COUNT(a.CLIENT_ID_MD5) TOTAL, IsNull(c.FIRM, 'Unknown') as Firm
FROM dbo.amazonlogs a
LEFT JOIN dbo.customers c ON c.CLIENT_ID_MD5 = a.CLIENT_ID_MD5
GROUP BY a.CLIENT_ID_MD5, c.FIRM ORDER BY COUNT(*) DESC;
This will give you a value of "Unknown" when records in the customers table don't exist. You could obviously drop that part and just return c.FIRM if you want to have actual nulls instead.
当客户表中的记录不存在时，这将为您提供“未知”值。如果您想要实际的空值，您显然可以删除该部分并只返回 c.FIRM。
回答by mo.
Change your INNER JOIN to an OUTER JOIN...
将您的 INNER JOIN 更改为 OUTER JOIN ...
SELECT a.CLIENT_ID_MD5, COUNT(a.CLIENT_ID_MD5) TOTAL, c.FIRM
FROM dbo.amazonlogs a
LEFT OUTER JOIN dbo.customers c
ON c.CLIENT_ID_MD5 = a.CLIENT_ID_MD5
GROUP BY a.CLIENT_ID_MD5, c.FIRM
ORDER BY COUNT(*) DESC;
回答by onedaywhen
WITH amazonlogs_Tallies
AS
(
SELECT a.CLIENT_ID_MD5, COUNT(a.CLIENT_ID_MD5) TOTAL
FROM dbo.amazonlogs a
GROUP
BY a.CLIENT_ID_MD5
),
amazonlogs_Tallies_Firms
AS
(
SELECT a.CLIENT_ID_MD5, a.TOTAL, c.FIRM
FROM amazonlogs_Tallies a
INNER JOIN dbo.customers c
ON c.CLIENT_ID_MD5 = a.CLIENT_ID_MD5
)
SELECT CLIENT_ID_MD5, TOTAL, FIRM
FROM amazonlogs_Tallies_Firms
UNION
SELECT CLIENT_ID_MD5, TOTAL, '{{NOT_KNOWN}}'
FROM amazonlogs_Tallies
EXCEPT
SELECT CLIENT_ID_MD5, TOTAL, '{{NOT_KNOWN}}'
FROM amazonlogs_Tallies_Firms;
