Another place when NULL gets tricky

Dealing with NULLs in SQL causes confusion in many cases. Two good articles cover a lot about NULLs in SQL server: 4 Simple Rules for Handling SQL NULLs and Gotcha! SQL Aggregate Functions and NULL.

I this the latest should have covered another situation, when aggregate functions return NULL in case of not satisfying the where condition.
Let's take this Books table as an example:

ID Title  Price
--------------------------------
1 C#  10
2 Database 15
3 VB6  30
3 VB.net  35

When you want to get the sum of all books that start with 'vb', you simply use SUM function like this:

Select sum(Price) from Books where title like 'vb%'

And the result would be 65

One may expect that if he asks for the sum of c++ books prices, the result will be zero (as we have none). for example, this query returns zero:

Select count(Price) from Books where title ='c++'

But with sum:

Select sum(Price) from Books where title ='c++'

The result is NULL. Why? Because, using the same logic described in the above articles, SQL will consider this as an absence of data (It couldn't determine the total price since it did not find any prices) if that makes sense.

This can cause issues when trying to read the value returned by this query in the application which expects a float value. A simple remedy for this would be using COALESCE or ISNULL: (I prefer to use COALESCE since it is standard.)

Select COALESCE(sum(Price),0) from Books where title ='c++'
Select ISNULL(sum(Price),0) from Books where title ='c++'

The result in this case is zero as desired.