SQL Outer Join Question 1

[Palmyra]

If there is no location in the Records table that matches a location in Records_Locations, how do I make a message display to indicate that? I though below would work, but obviously I'm off.

SELECT DISTINCT Records_Locations.Location_Code,
Records.opendate,
Records.tkloc,
CASE
WHEN Records.tkloc IS null
THEN Records_Locations.Location_Code
ELSE Records.tkloc
END AS Location
FROM Records_Locations
LEFT OUTER JOIN Records ON Records_Locations.Location_Code = Records.tkloc
WHERE Records.opendate = '9/6/2011'

Thanks for any help.

 

[gmmastros]

Yeah.... that's not going to work the way you wrote it. You see... your WHERE clause is effectively making your LEFT JOIN query behave as an inner join.

Just to be clear, the left table in this query is Records_Location and the right table is Records. With a left join query, you are guaranteed to get at least one row in the output for each row in the left table. If there is a match (or multiple matches), the data in the right table's (Records) columns will contain actual values. If there is no match, the data in the right table will all contain NULL.

Your where clause is filtering on the right table's opendate column. If there is no match between the tables, the right table's opendate column will be null, which is not the same as '9/6/2011', so those rows will effectively be filtered out of the result set.

To fix this problem, simply put the WHERE clause criteria in the ON clause instead of the where clause, like this:

SELECT DISTINCT Records_Locations.Location_Code, 
                Records.opendate, 
                Records.tkloc,
                CASE 
                      WHEN Records.tkloc IS null 
                      THEN Records_Locations.Location_Code
                      ELSE Records.tkloc
                      END AS Location    
FROM       Records_Locations
           LEFT OUTER JOIN Records 
             ON  Records_Locations.Location_Code = Records.tkloc
             [!]AND Records.opendate = '9/6/2011'[/!]

This way, if there is no match (based on the ON clause), you will still get a row in the output where your CASE/WHEN statement can control the results.

By the way, I would have written this using the COALESCE function instead of CASE/WHEN, like this:

SELECT DISTINCT Records_Locations.Location_Code, 
                Records.opendate, 
                Records.tkloc,
       Coalesce(Records.tkloc,Records_Locations.Location_Code) AS Location    
FROM       Records_Locations
           LEFT OUTER JOIN Records 
             ON  Records_Locations.Location_Code = Records.tkloc
             [!]AND Records.opendate = '9/6/2011'[/!]

Note that performance is likely to be the same. I prefer coalesce because it effectively acts like a special case of Case/When. I can tell at a glance that we are checking for the NULL condition. Case/When can get pretty complicated, but coalesce only checks for nulls. Basically, I think it makes the query easier to understand.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Palmyra]

Perfect sense! - I'll take your advice about the coalesce.

Thanks.