Common SQL Mistakes 1

This BlogPost was written by Andre Speek. Click here for other BlogPosts.

Often when people ask me for help with a Query, it's not because they get errors. If that's the case, the error most often provides a good description on what is wrong and will give quite a good hint on how to solve the problem.

So most questions arise from Queries that give results without any error. But the numbers just don't add up. So what could be the mistake?

Finding the mistake might seem hard. But it becomes a lot easier when you know some of the quircks of SQL Server. So today, let's have a look at some of these and see why they don't give the results as expected.

The order of a case

When constructing a case, it makes a difference in what order you evaluate the different cases. So you should be aware how this affects your results. Have a look at this example:

select Name, xType,
case
  when Name like 'A%' then '1'
  when len(Name) < 5 then '2'
  when len(Name) >= 4 then '3'
  else '0'
end
from Sysobjects
where xType = 'U'
Order by Name

So, when the length of Name is 4 characters, you might expect '3'. But instead, you will get '1' or '2'. So why is that?

Well, if the Name starts with A, you will definitely get '1'. And if the length of the Name is exactly 4 characters and does not start with A, you will get '2'.

That's because the evaluation of a Case is done line by line. If the first line evaluates to true, that value will be used and all the following evaluations will be skipped.

So if you are sure a condition is true and should give you a different output than the one you are getting? Check the conditions before that one if there is no other condition that will also be true.

Dividing Integers

Another common mistake which might be overlooked is dividing integers. It will not produce an error but dividing one integer by another integer will result in an integer as well.

Check this query:

select 2 / 4

Now this should give 0.5 as a result, right?

Wrong! It will give you 0. No errors here, it is a valid Query. But it will give unexpected numbers in your report.

Casting the result as a float might solve the problem, right? So let's try this one:

select cast(2 / 4 as float)

Unfortunately, this will not make it work since you are still dividing an integer by another integer. The result will still be an integer and only then be casted to a float.

So you need to cast one of them to a float for the correct result:

select cast(2 as float) / 4

Null in averages

Another one that might make you frown at the results is the fact that Null is really nothing. Take a look at this query:

with tbl as (
  select 'A' as Code, 3.0 as Price
  union
  select 'B' as Code, 3.0 as Price
  union
  select 'C' as Code, 0.0 as Price
  union
  select 'D' as Code, null as Price
)
select avg(Price) from tbl

The total here is 6 and we have 4 records. So the average should be 1.5, right?

Wrong! Yes, the total is 6. But null is really nothing, so the average is calculated for 3 Prices only. The average here will be 2, and not the 1.5 you might expect.

So if your column can contain Null values and you want it to be taken into account for the average, you should do something like:

select avg(isnull(Price, 0)) from tbl

Or calculate the average yourself:

select sum(Price) / count(*) from tbl

But that one is tricky as well. Why? Well, let's go for the next common mistake!

Count on nullable columns

As said, Null is really nothing. So that goes not just for averages. It also goes for counting. Check out this one:

with tbl as (
  select 1 as SomeField
  union
  select null as SomeField
)
select count(*), count(SomeField)
from tbl

Here you can see that count(*) will tell you that there are two rows in the table. But count(SomeField) will tell you there is only one!

For the second row, SomeField is null so it is nothing. That's why it's ignored when counting.

To be safe, always use count(*) if you need the number of records from a result set. And use count(SomeField) only and intentionally if you want to skip the Null values.

Union vs Union All

The Union is used to aggregate two or more result sets into one. With the restriction that all the result sets have the same columns.

There two types of Unions: The Union and the Union All. There is a subtle but significant difference between Union and Union All.

Try this one with the Union and then the Union All:

select Name
from sysobjects
where xType = 'U'
union
select Name
from sysobjects
where xType = 'U'

As you will notice, the Union All will give you twice the number of records than the regular Union. That's because both Select Statements are the same.

The regular Union however will remove duplicates. A bit like a Group By or a Distinct selection. The Union All will simply get both result sets and aggregate them into one final result set. With duplicates if they occur.

In most cases you would use the regular Union so always start with that one. If you need the duplicates for a special reason, only then go for the Union All.

That's it for today!

So did you recognize some of these common mistakes? How often you run into these cases depends on the quality of the database design. For example, quantity and amount columns should have a default value of 0 instead of Null. Booleans should have 0 and 1, not Null.

Alas, we don't live in an ideal world. So expect the unexpected to occur. But knowing some of these common mistakes will sure help you to troubleshoot and fix your Query!

So, what other mistakes do you encounter and should be covered in a follow up post?

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!