0

If I run a statement such as SELECT * FROM stuff WHERE something=NULL I will get an empty result set.

I know that the solution is to use something IS NULL. I also understand why: SQL interprets NULL as unknown, and it makes logical sense that if something is unknown, it can’t match something else.

What puzzles me is the meaning of the result set. I have always understood that you can’t match something=NULL, so why is is acceptable? It would suggest that under some circumstances, one might get a different result?

Alternatively, why does SQL not accept something=NULL as an idiom equivalent to IS NULL? It wouldn’t be the only idiom which doesn’t have a literal interpretation (such as count(*) vs SELECT *).

The question is: since something=NULL is not an error, does it mean something useful?

Andriy M
  • 22,983
  • 6
  • 59
  • 103
Manngo
  • 2,819
  • 8
  • 34
  • 56
  • 1
    So if instead of a literal NULL you have a=b where b happens to be null, you want the equality operator to automatically take on the meaning of IS (NULL)? – Andriy M Feb 09 '17 at 20:51
  • @AndriyM Not necessarily. I’m not recommending anything of that sort, though a special case could be made for interpreting a literal =NULL that way if it has no other meaning. However, I do see in your comment the seeds of a possible explanation: WHERE A=B may include nulls in either side, which should be omitted. WHERE A=NULL is a specific literal which simply forces the issue. Any chance of turning something like that into an answer? – Manngo Feb 09 '17 at 20:59
  • I was merely pondering over possible implications of your suggestion and didn't really have an answer there. At the same time, you seem to have found one, and I'm perfectly happy for you if that's the case. Feel free to use the point I was asking about to expand your conclusions into an answer of your own. – Andriy M Feb 09 '17 at 21:06

1 Answers1

2

You can compare things to NULL.

something=NULL is not an error.

It is a Boolean expression that evaluates to unknown. SQL uses three valued logic. The possible results of a Boolean expression are true, false or unknown.

The WHERE clause only returns rows where the predicate evaluates to true.

Whilst comparing to a literal null with =null is rarely (if ever) useful it is certainly useful to be able to use an col = something predicate against a column which may contain nulls and the semantics of how = something operates against null are the same there.

Martin Smith
  • 84,644
  • 15
  • 245
  • 333