Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm not sure of the right syntax to use.

Is it

select * from tlb
where flag is NULL


select * from tlb
where flag = NULL

Is the answer the same for oracle, sqlserver and Postgres?

Note: I reviewed http://programmers.stackexchange.com/help/on-topic and it seems ok there. I thought this question might help many others struggling with the null equality issue that I've frequently encountered in the different databases.

share|improve this question

closed as off topic by Karl Bielefeldt, gnat, Yannis Rizos Sep 20 '12 at 19:06

Questions on Programmers Stack Exchange are expected to relate to software development within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

Have you tried testing what happens in each of those databases? This is really easy to test... –  Andres F. Sep 20 '12 at 15:27
I am not at a computer with them both installed. Installing them just to find it out is a bit of a chore. –  Michael Durrant Sep 20 '12 at 18:22
So you expect others to answer your question because trying it yourself is "a bit of a chore"? Let me refer you to this fantastic article, What have you tried?. –  Andres F. Sep 20 '12 at 18:28
A more general question about cross-DB NULL testing may have snuck in under the threshold, for idle questions like this I look to sqlfiddle.com, which lets me try out SQL on a bunch of different databases. –  Jamie Sep 21 '12 at 2:50
@MichaelDurrant: We have issues with people asking questions that show no effort on their part. Note that when people answer here, they do volunteer their time. –  Martijn Pieters May 2 at 10:42
show 2 more comments

1 Answer

up vote 11 down vote accepted

Use the SQL standard IS NULL or IS NOT NULL to test for NULL

Quote from the Postgresql docs:

Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard.

share|improve this answer
I believe SQL Server also conforms to this, NULL = NULL generally returns NULL, which in turn is not interpreted as a truthy value. If you interpret NULL as unknown (almost literally), it makes perfect sense. Is unknown equal to unknown? Unknown. –  Daniel B Sep 20 '12 at 15:50
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.