Query Mistakes

I thought that I should share a couple of lessons about T-SQL that I have seen even experienced database developers get wrong.

When 1.0 and 1 Are Not the Same

This one really caught me off guard when I first saw it. I was writing some SQL at my first job out of college and my manager and I were talking in his office. He said, “Did you test it?” referring to some code I had written in a SQL stored procedure used to generate a report. I don’t remember what we said after that, but thankfully for all of us, he also knew some SQL. He must have had a gut suspicion that the resulting numbers in the math formula where somehow wrong. He opened the stored procedure and started breaking the formula apart and looking at the different parts of the formula and testing it for me. Wouldn’t you know that the numbers were off and what made the difference was to change a value by adding the “.0” to the end of the number in the formula. That’s right, adding the “.0” in the formula fixed it so that it produced the correct value.

Here is an example that show the phenomena happening.

SELECT 1 / 3 * 100 Percent1
    ,1 / 3 * 100.0 Percent2
    ,1.0 / 3.0 * 100 Percent3
    ,1.0 / 3.0 * 100.0 Percent4
    ,100.0 * 1 / 3 Percent5

Here are the results.

Why? Implicit type-casts are going on here. The number 1 is and integer and 1.0 is a floating-point number. So, if you are getting some wrong results from SQL that has some number calculations in it, look at it from a data-type perspective. You might find the fix faster than you expected and with little head scratching.

If you want to learn more details about this you can find some documentation on data type conversion.

You might be thinking “Aren’t 1.0 and 1 the same number?” Consider this query.

SELECT '1.0 and 1 are the same' Answer
WHERE 1.0 = 1

It has the results I was expecting–'1.0 and 1 are the same'. This is because 1 is implicitly converted to a float (1.0) and then the comparison is done. There is an order of precedence to the type casting that helps you know which type a value will be converted to and also helps explain the results of the above query.

When a Left Join is Not a Left Join

Another pitfall that I have seen developers fall into is when you need to filter the table with which you are doing a left outer join. To illustrate this, consider a query meant to return the number of orders every person made of a certain product.

SELECT COUNT(*)
    ,P.PersonId
FROM Person P
LEFT JOIN Order O ON P.PersonId = O.PersonId
WHERE O.ProductId = 3
GROUP BY P.PersonId

The above query has the problem of requiring the ProductId to be 3 to show up in the results, so it is effectively an inner join instead of a left join. There are a few ways of fixing this problem. Here is my preferred way since it is the least amount of code. Move the filtering to the ON clause like this.

SELECT COUNT(*)
    ,P.PersonId
FROM Person P
LEFT JOIN Order O ON P.PersonId = O.PersonId AND O.ProductId = 3
GROUP BY P.PersonId

The above query doesn’t add an OR O.ProductId IS NULL condition in the WHERE clause to get the values that are being filtering out (which can sometimes slow a query) and it doesn’t have the overly verbose version of left joining on a sub-select that filters by ProductId=3, which is why it is my favorite.

Conclusion

Just because a query returns results with no errors doesn’t mean that it is correct. I hope the examples of some SQL pitfals have been useful to you.