How to have SQL INNER JOIN accept null results
69,160
Solution 1
Replace INNER JOIN
with LEFT JOIN
Solution 2
use LEFT JOIN
instead of INNER JOIN
Solution 3
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
http://www.w3schools.com/sql/sql_join.asp
Solution 4
An inner join excludes NULLs; you want a LEFT OUTER join.
Solution 5
Change your INNER JOIN to an 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;
Comments
-
Tom Redman over 3 years
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:
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:
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?
-
evilone over 12 yearsLEFT JOIN returns rows even when there's no match in other table that you are joining. INNER JOIN will not, it returns only rows if there's a match in other table
-
evilone over 12 yearsYou have a typo in your query - LEFT OURER JOIN