asked on

SQL Null not returning results

Ok I am writing a very simple statement but I must be missing something...

select accountidname, tsp_srvc_equipmentididname from incident
where subjectidname='turf equipment' and statecode=0

The above statement returns all the values I expect (results are posted below in the CODE SNIPPET).

However, when I run the same query but wanting to show only the results where tsp_srvc_equipmentididname is null

select accountidname, tsp_srvc_equipmentididname from incident
where subjectidname='turf equipment' and statecode=0 and tsp_srvc_equipmentididname='null'

I get NOTHING returned.

CAROLINA LAKES GOLF CLUB, LLC	062704.JTE
CAROLINA LAKES GOLF CLUB, LLC	NULL
FIRETHORNE GC	NULL
WILSON COUNTRY CLUB	NULL
THE GOLF CLUB AT STAR FORT, INC.	NULL
GAFFNEY CC	NULL

Open in new window

ASKER CERTIFIED SOLUTION

NIMTUG_Simon

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

NIMTUG_Simon

select accountidname, tsp_srvc_equipmentididname from incident
where subjectidname='turf equipment' and statecode=0 and tsp_srvc_equipmentididname IS NULL

Open in new window

r270ba

ASKER

Perfect...I knew I was missing something easy...I was doing ='null' and isnull and = null but I did not know to put a space between is and null :).

Thanks!

Guy Hengel [angelIII / a3]

NULL is special:

select accountidname, tsp_srvc_equipmentididname from incident
where subjectidname='turf equipment' and statecode=0 and tsp_srvc_equipmentididname IS NULL

Open in new window

NIMTUG_Simon

ISNULL is actually a function which you can use to replace the value with a set value is the original value is null.

So if you have a table (t1) with

col1
=====
'A'
'B'
NULL
'C'

you can have a query which goes

Select ISNULL(col1, 'No Value') as col1 FROM t1

You will get

'A'
'B'
'No Value'
'C'

r270ba

ASKER

sweet...thanks a lot guys!