SQL server not listing records with NULL values

Question

I faced a strange phenomenon today. I ran the below query to Find all requests with waits other than BROKER_RECEIVE_WAITFOR

select * from sys.dm_exec_requests where session_id > 55 
and wait_type not like ('BROKER_RECEIVE_WAITFOR') 

The query ran fine, But it failed to list any request with No waits, ie with wait_type as NULL(there were quite a lot of running queries without any waits at this time).

Why is this happening, Isn't it supposed to list all records with wait_type not like 'BROKER_RECEIVE_WAITFOR' including Nulls?

I'm running SQL server 2008R2 SP3.

Answer 1

The reason for this is that a NULL represents an unknown value.

NULL is not LIKE anything and not NOT LIKE anything either.

Try running the following 2 statements on your system:

SELECT CASE WHEN null like '%a%' THEN 'true' ELSE 'false' END;
SELECT CASE WHEN null not like '%a%' THEN 'true' ELSE 'false' END;

That should return false for both statements.

So your query should look like this:

select * from sys.dm_exec_requests where session_id > 55 
and (wait_type not like ('BROKER_RECEIVE_WAITFOR') or wait_type is null)

Answer 2

You'd have to add the additional check for null

select * from sys.dm_exec_requests where session_id > 55 
and wait_type not like ('BROKER_RECEIVE_WAITFOR') or wait_type is null

You may get a better understanding of this situation by checking out the information in this link:

http://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/

To show nulls for only session_id > 55, you'd have to use parenthesis to force the correct boolean evaluation:

select * from sys.dm_exec_requests 
where session_id > 55 
and (wait_type not like ('BROKER_RECEIVE_WAITFOR') or wait_type is null)

Answer 3

You dont need LIKE here.

In fact this will give you incorrect semantics as _ is a wild card for matching any single character. So your query would incorrectly exclude certain other strings too (though this is hypothetical as there is no wait type that it can clash with in practice).

One other possible approach to give your desired semantics is described here

select * 
from sys.dm_exec_requests 
where session_id > 55 
and Exists (SELECT wait_type EXCEPT SELECT 'BROKER_RECEIVE_WAITFOR') 

Answer 4

If you want to include nulls I would write it like this:

select * from sys.dm_exec_requests 
where session_id > 55 
  and COALESCE(wait_type,'') not like ('BROKER_RECEIVE_WAITFOR') 

a number of times I've found optimizers handle COALESCE faster than or xxx is null