Tell me more ×
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.
up vote 0 down vote favorite

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

Is it 

select * from tlb
where flag is NULL

or

select * from tlb
where flag = NULL

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

share|improve this question
1  
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

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 in the FAQ. 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 closed questions here.

1 Answer

up vote 10 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
1  
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

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