Checking database for NULL boolean value

Question

I have a field in a table that is boolean, a range of records have no value (not true or false). How do I write my SQL statement to find these?

I have tried the following SQL statements without success:

1) SELECT * FROM table WHERE field = NULL
2) SELECT * FROM table WHERE field != TRUE AND field != FALSE

Any help would be greatly appreciated.

Many Thanks, Ben

NULL is not a value, it's absence of value. Thus no value could be compared to the absence of value. — Milen A. Radev
– Milen A. Radev, Commented Jun 11, 2009 at 15:23
@Milen: Pretty much every other language manages this just fine. IMO it's extremely dumb that SQL needs a different operator for this — Draemon
– Draemon, Commented Jul 9, 2009 at 11:57

Mitch Wheat · Accepted Answer · 2014-04-12 23:33:39Z

31

In TSQL, you need to use IS rather than = when comparing with NULL:

SELECT * FROM table WHERE field IS NULL

answered Jun 11, 2009 at 14:58

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vivek S. · Accepted Answer · 2015-08-22 09:01:52Z

6

Try

select * from table where field IS null

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

answered Jun 11, 2009 at 14:58

Nick DeVore

10.2k3 gold badges41 silver badges41 bronze badges

samjudson · Accepted Answer · 2009-06-11 14:58:29Z

4

You want IS NULL I believe:

SELECT * FROM table WHERE field IS NULL

answered Jun 11, 2009 at 14:58

samjudson

57k7 gold badges61 silver badges69 bronze badges