Chris McGrath’s Blog

Just another Developer’s Blog

Posts Tagged ‘SQL

SQL Generators

leave a comment »

SQL Generators are known for how many brackets they love to put in. So many in fact it made a simple query hard to understand.

Like most professional developers I don’t spend too much time using generators, I much prefer to write the query myself. And I generally stay away from DataSets completely. But today I had to paste my SQL into a query in a DataSet, and I noticed how Microsoft had tried to optimise the Builder so when it reformatted my SQL it didn’t have so many brackets.

The problem was it removed the useful brackets and added useless ones.

Here’s what I entered…

(x1 = x2 AND y1 = y2) OR (n1 = n2 AND m1 = m2)

And here’s what it optimised it to…

(x1 = x2) AND (y1 = y2) OR (n1 = n2) AND (m1 = m2)

How completely stupid! The main reason anyone adds brackets in SQL is to make the order of operations absolutely clear. Yet it removes those ones and adds sets around single statements.

The query might be correct but a human has trouble – not everyone knows the order of operations with booleans.

Written by Chris McGrath

August 31, 2009 at 4:16 pm

Posted in Blog

Tagged with ,

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 = 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 = 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.

Written by Chris McGrath

July 17, 2009 at 9:10 am

Posted in Blog

Tagged with ,