Wednesday, September 24, 2014

Why is there no == operator in SQL?

Why is there no == operator in SQL that yields true if both operands are either null or have the same value?

Here's the truth table for the = operator:

= 42 13 null
42 true false null
13 false true null
null null null null

This has some implications. The statement
select * from t where col1 = col2
won't return a record where both col1 and col2 are null.

I suspect that in most cases this is not what the author of such a statement wants. Therefore, they will rewrite the query so:

select * from t where ( a is null and b is null) or ( a = b)

Now, if there were a == operator with this truth table:

== 42 13 null
42 true false false
13 false true false
null false false true
it would definitely make my life easier (and would not cost the database companies too much money to implement).

Maybe I am all wrong and there is such a thing somewhere. If you know of such an operater in any database product, please let me know!

No comments:

Post a Comment