CODEWITHSUNDEEP
sqlsql-serverjoininner-join
Tom Redman
Tom Redman

Reputation: 5650

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 (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?

Upvotes: 46

Views: 81809

Answers (7)

onedaywhen
onedaywhen

Reputation: 57093

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;

Upvotes: 1

Ian Nelson
Ian Nelson

Reputation: 58783

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

Upvotes: 4

mo.
mo.

Reputation: 4255

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;

Upvotes: 1

Punit
Punit

Reputation: 1120

use LEFT JOIN instead of INNER JOIN

Upvotes: 15

ChrisLively
ChrisLively

Reputation: 88092

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.

Upvotes: 1

Joe
Joe

Reputation: 42666

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

Upvotes: 3

evilone
evilone

Reputation: 22750

Replace INNER JOIN with LEFT JOIN

Upvotes: 154

Related Questions