THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Teaser: Where Clause Gone Wild

Try to guess what this WHERE clause is supposed to do.



r.ApptId IS NULL

AND r.DATE >= ISNULL(NULL , '1/1/1900')

AND r.DATE < DATEADD(d , 1 , ISNULL(NULL , '1/1/3000'))


--Filter on resource


AND r.DoctorResourceID IN ( NULL ) )


AND --Filter on facility


AND r.FacilityID IN ( NULL ) )


AND --Filter on Inactive

ISNULL(r.inactive , 0) = 0


ISNULL(g.LAST , '') + ISNULL(g.FIRST , '')


If you think I made that up, then you are in for a surprise. This is actually part of a query, I found it here:

This NULL IS NULL and NULL IS NOT NULL stuff is just killing me. I wonder why this person did not get an answer yet.....Enjoy your weekend  :-)

Published Friday, July 25, 2008 3:24 PM by Denis Gobo
Filed under:



Fabiano Neves Amorim said:

Wow, NULL IS NOT NULL is just killing us :-(.

July 25, 2008 3:32 PM

jchang said:

I have seen code like this before, it is called "job insurance" style

July 25, 2008 5:07 PM

jchang said:

and people thought terry childs was paranoid

July 25, 2008 5:13 PM

arb said:

This looks like it was dynamically generated. The "IN ( NULL )" sections in particular look to be dynamically generated - I would guess that the parent code has a string of IDs and it is supposed to generate something like "r.DoctorResourceID IN ( 1243, 1244, 236 )" but somehow the strings are empty/NULL.

July 25, 2008 7:28 PM

Rachel Appel said:

Oh my, is someone posting that at or what? The null is not null part is a riot!

July 25, 2008 7:55 PM

Itaborai, D. L. said:

ok, this looks kinda scary but its obvious what it is doing. It probably came from a stored procedure with a lot of optional parameters.

For example, let´s assume we are filtering on a resource, then we would have.


AND r.DoctorResourceID IN ( 0xDEADBEEF ) )


wich would retrieve all rows from the recall table that referenced the doctor resource association table wich had a primary key of 3735928559.

I´m not saying that it is pretty or even that I would use such an abomination ... well ... maybe when I´m given a crappy deadline.

I just think that he could at least leave the parameters unchanged instead of replacing them with NULLs all over the place. This is typical enterprise suckiness at its best ... At least it´s not 200 lines long and references half the tables in the application´s model.

July 25, 2008 9:19 PM

Itaborai, D. L. said:

as I said before, this probably came from a sproc with optional parameters.

It turns out, you actually need the NULL IS NOT NULL part. It works as a safeguard so the "AND r.DoctorResourceID IN ( NULL )" does not get evaluated thanks to boolean short-circuiting.

Given a non-null value, it would evaluate both parts and return true.

Given a null value, it evaluates the parenthesized AND expression as false, but the expression after the OR would evaluate as true.

The poor fella seems to be using string interpolation. I´m not a developer anymore, but I always remember seeing the puzzled eyes of junior developers staring at a query like this. They still had souls back then.

July 25, 2008 9:35 PM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement