How to have SQL INNER JOIN accept null results

Question

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?

Answer 1

Replace INNER JOIN with LEFT JOIN

Answer 2

use LEFT JOIN instead of INNER JOIN