How to have SQL INNER JOIN accept null results

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 (FI开发者_StackOverflowRM) 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?

Replace INNER JOIN with LEFT JOIN

use LEFT JOIN instead of INNER JOIN

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

An inner join excludes NULLs; you want a LEFT OUTER join.

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.

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;

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;