How to make =NULL work in SQLite?

Question

Given the following table:

Table: Comedians
=================

Id    First    Middle    Last
---  -------  --------  -------
 1     Bob      NULL     Sagat
 2    Jerry     Kal      Seinfeld

I want to make the following prepared query:

SELECT * FROM Comedians WHERE Middle=?

work for all cases. It currently does not work for the case where I pass NULL via sqlite3_bind_null. I realize that the query to actually search for NULL values uses IS NULL, but that would mean that I cannot use the prepared query for all cases. I would actually have to change the query depending on the input, which largely defeats the purpose of the prepared query. How do I do this? Thanks!

How would a user search for a comedian without a middle name? — Mithrandir, Feb 01 '12 at 20:05
@Mithrandir This isnt a user facing function, it is for internal use. Essentially, I pass a pointer (char*) to the function. If the pointer is NULL I use `sqlite3_bind_null` but if it is the string 'NULL' I use the string function. — chacham15, Feb 01 '12 at 20:08

score 7 · Accepted Answer · answered Feb 01 '12 at 20:03

7

You can use the IS operator instead of =.

SELECT * FROM Comedians WHERE Middle IS ?

answered Feb 01 '12 at 20:03

dan04

87,747
23
163
198

2

@dotje: In PostgreSQL you can use `a IS NOT DISTINCT FROM b`. Slightly more expensive than `a = b`. – Erwin Brandstetter Feb 01 '12 at 20:17

score 4 · Answer 2 · answered Feb 01 '12 at 20:03

Nothing matches = NULL. The only way to check that is with IS NULL.

You can do a variety of things, but the straight forward one is...

WHERE
  middle = ?
  OR (middle IS NULL and ? IS NULL)

If there is a value you know NEVER appears, you can change that to...

WHERE
  COALESCE(middle, '-') = COALESCE(?, '-')

But you need a value that literally NEVER appears. Also, it obfuscates the use of indexes, but the OR version can often suck as well (I don't know how well SQLite treats it).

All things equal, I recommend the first version.

score 2 · Answer 3 · answered Feb 01 '12 at 20:03

2

NULL is not a value, but an attribute of a field. Instead use

SELECT * FROM Comedians WHERE Middle IS NULL

answered Feb 01 '12 at 20:03

wallyk

56,922
16
83
148

this isnt a generic solution since it doesnt allow me to test equality with something other than null – chacham15 Feb 01 '12 at 20:06
@chacham15: There is no generic solution using NULL, since it is not a value. Perhaps you should use a [sentinel value](http://en.wikipedia.org/wiki/Sentinel_value) instead. – wallyk Feb 01 '12 at 20:16

score 0 · Answer 4 · edited May 23 '17 at 11:43

0

If you want match everything on NULL

SELECT * FROM Comedians WHERE Middle=IfNull(?, Middle)

if want match none on NULL

SELECT * FROM Comedians WHERE Middle=IfNull(?, 'DUMMY'+Middle)

See this answer: https://stackoverflow.com/a/799406/30225

edited May 23 '17 at 11:43

Community

1
1

answered Feb 01 '12 at 20:04

Preet Sangha

64,563
18
145
216

How to make =NULL work in SQLite?

4 Answers4

Linked