What does this SQL Query mean?

Question

I have the following SQL query:

select AuditStatusId 
from dbo.ABC_AuditStatus 
where coalesce(AuditFrequency, 0) <> 0

I'm struggling a bit to understand it. It looks pretty simple, and I know what the coalesce operator does (more or less), but dont' seem to get the MEANING.

Without knowing anymore information except the query above, what do you think it means?

Answer 1

select AuditStatusId 
from dbo.ABC_AuditStatus 
where AuditFrequency  <> 0 and AuditFrequency  is not null

Note that the use of Coalesce means that it will not be possible to use an index properly to satisfy this query.

Answer 2

It looks like it's designed to detect a null AuditFrequency as zero and thus hide those rows.

Answer 3

From what I can see, it checks for fields that aren't 0 or null.

Answer 4

COALESCE is the ANSI standard function to deal with NULL values, by returning the first non-NULL value based on the comma delimited list. This:

WHERE COALESCE(AuditFrequency, 0) != 0

..means that if the AuditFrequency column is NULL, convert the value to be zero instead. Otherwise, the AuditFrequency value is returned.

Since the comparison is to not return rows where the AuditFrequency column value is zero, rows where AuditFrequency is NULL will also be ignored by the query.

Answer 5

I think it is more accurately described by this:

select AuditStatusId 
from dbo.ABC_AuditStatus 
where (AuditFrequency IS NOT NULL AND AuditFrequency != 0) OR 0 != 0

I'll admit the last part will never do anything and maybe i'm just being pedantic but to me this more accurately describes your query.

Answer 6

The idea is that it is desireable to express a single search condition using a single expression but it's merely style, a question of taste:

---

One expression:

WHERE age = COALESCE(@parameter_value, age);

Two expressions:

WHERE (
       age = @parameter_value
       OR
       @parameter_value IS NULL
      );

---

Here's another example:

---

One expression:

WHERE age BETWEEN 18 AND 65;

Two expressions

WHERE (
       age >= 18
       AND 
       age <= 65
      );

---

Personally, I have a strong personal perference for single expressions and find them easier to read... if I am familiar with the pattern used ;) Whether they perform differently is another matter...