CODEWITHSUNDEEP
c++sqlsqlite
chacham15
chacham15

Reputation: 14251

How to make =NULL work in SQLite?

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!

Upvotes: 7

Views: 904

Answers (4)

Preet Sangha
Preet Sangha

Reputation: 65496

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

Upvotes: 0

wallyk
wallyk

Reputation: 57774

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

SELECT * FROM Comedians WHERE Middle IS NULL

Upvotes: 2

MatBailie
MatBailie

Reputation: 86715

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.

Upvotes: 4

dan04
dan04

Reputation: 90995

You can use the IS operator instead of =.

SELECT * FROM Comedians WHERE Middle IS ?

Upvotes: 8

Related Questions