Chris McGrath’s Blog

Just another Developer’s Blog

A really sneaky SQL join problem

leave a comment »

I was creating a query the other day and it wasn’t returning the amount of rows I was expecting. I did quickly figure out what was causing me but how to solve it stumped me. Luckily a co-worker who was much more experienced with SQL knew how to fix it. But it’s a case that those of us who don’t live and breath SQL probably don’t know about and it’s so easy to get this wrong all developers should be aware of it.

Here’s the problem…

You have a person table and a phone number table. A phone can be work or home. In the person table there is John, Michael, Chris and Dan. John has neither number entered. Michael has both entered, Chris just has work number entered and Dan has home number entered.

You want to display a list of every Person and if they have their work number entered, to grab that as well, else null.

Easy right? How about giving it a go?

Did you come up with this…

select Person.*, Phone.Number
from Person left outer join Phone on person.id = phone.personid
where Phone.Type = ‘w’ or Phone.Type is null

That was easy, the left outer join stops the person row from disappearing if no phone number exists and we get rid of the home numbers through the where – we are also careful not to remove the items without a phone number with a “OR IS NULL” check.

Well you’re wrong. If you were to run it only 3 of the 4 people would show up. Can you think why? Here’s a clue, the person would be Dan.

If you still haven’t got it, the Dan rows that exist before the where is just when type = ‘h’. It doesn’t have a null value their like John has. So the where then says, “is it w? is it null? well get rid of it.”

But how do we fix this? It doesn’t look like we can, our check is happening too late we want to filter the phone table and then outer join it. Not possible? Must require some complex subquery? Well actually it doesn’t…

select Person.*, Phone.Number
from Person left outer join Phone on person.id = phone.personid
AND Phone.Type = ‘w’

That’s it. We move the check inside the join.

It’s quite a nice solution for it really. I think as long as you are aware of this you won’t have any problem but I do wonder how many bugs are out there exist because this wasn’t taken into consideration.

Advertisements

Written by Chris McGrath

July 17, 2009 at 9:10 am

Posted in Blog

Tagged with ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: