2

Inspired by a StackOverflow question (Why doesn't SQL support "= null" instead of "is null"?).

Is there a DBMS that actually supports the = NULL syntax?

Community
  • 1
Richard
  • 6,393
  • 8
  • 42
  • 62
  • Why do you want this? I'd argue that the syntax is confusing to anyone who understands what NULL really means in the context of a database. SQL Server, for example, allows you to change the default behavior, but it WILL be deprecated and it can be misinterpreted by your co-workers (or break if their settings are default). – Aaron Bertrand Aug 22 '11 at 02:42
  • It's a question that popped into my mind from reading this post. – Richard Aug 22 '11 at 12:52

4 Answers4

9

Some implementations of SQL do recognise x = NULL as equality, the ISO/ANSI standard on the other hand does not. In SQL Server for instance, SET ANSI_NULLS OFF results in (NULL = NULL) = true.

SET ANSI_NULLS OFF
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END

SET ANSI_NULLS ON
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END
Mark Storey-Smith
  • 31,687
  • 8
  • 89
  • 124
  • 1
    Oh, beautiful. That's what the ANSI_NULLS thing is for. Nice. – Richard Aug 19 '11 at 23:09
  • 2
    The old sysbase style syntax is more readable in constructs like where x in (null, 1, 2) which you have to rewrite as where y is null or x in (1, 2). BTW even ANSI isn't stringent about NULL can't be equal to NULL. Just see how it handles GROUP BY. I really liked the old syntax, but I abounded fighting against ANSI defaults. – bernd_k Aug 20 '11 at 09:10
  • 4
    Be aware that the ANSI_NULLS OFF option changes the meaning of certain expressions. You can break or change the behaviour of your code just by changing the ANSI_NULLS option. Also certain SQL Server features are disabled when ANSI_NULLS is OFF. ANSI_NULLS OFF is a deprecated feature and really should be avoided unless it's essential for backawards compatibility reasons. – nvogel Aug 20 '11 at 09:54
  • 1
    Note for MSSQL Server. ANSI_NULLS OFF is deprecated and in future version it ill be always ON. Any attempt to set it OFF ill raise an error. https://msdn.microsoft.com/en-us/library/ms188048.aspx – jean Aug 04 '15 at 18:29
4

You can configure Postgres to see NULL=NULL as true. See the documentation on transform_null_equals for more details. End of Postgres specific part of the answer.

I do not recommend to use this setting to make NULL equal another NULL since NULL has the meaning of being unknown. As you don't know if two unknown things refer to the same object (value), comparing NULL with something should always result in NULL (comparison result is unknown). It would have been better to call NULL differently – for example UNKNOWN – but SQL chose not to do so.

If you want to see if something is unknown use thing is NULL.

If you want to see if two things are equal or both are NULL use thing1 is not distinct from thing2. See the page end of Postgre's comparison functions for more information on this syntax. Firebird's documentation on the matter also includes a table which shows sample comparisons and their results. Wikipedia has the tables as well. IBM's DB2 does also support the distinct predicate according to their documentation.
Unfortunately, it looks like neither Microsoft with SQL Server nor Oracle with MySQL do support it. For MySQL their is a similar feature written as <=>.

SQL 1999 defines a distinct predicate (ISO/IEC 9075-2:1999 (E), section 8.13) as follows (cases for array types not shown here, slightly reformatted due to this platform's restricted formatting support):

i) ‘‘X IS DISTINCT FROM Y’’ is false if either:
1) X and Y are the null value, or
2) X = Y according to Subclause 8.2, ‘‘comparison predicate’’.
ii) Otherwise, ‘‘X IS DISTINCT FROM Y’’ is true.

So for the negated case is not distinct from:

  • The comparison yields either TRUE or FALSE
  • If both values are NULL it yields TRUE
  • If one value is NULL it yields FALSE
  • Otherwise it yields the result of first value=second value.
Augustus Kling
  • 181
  • 4
3

You have to remember that NULL isn't a constant, but rather is an unknown. You can't be equal to an unknown because you don't know the value of NULL.

mrdenny
  • 26,988
  • 2
  • 42
  • 81
0

There's a possible answer that 'all SQL DBMS support = NULL', but that is true in the SET clause of an UPDATE statement, not in a context that requires a conditional. That context would not accept IS NULL of course (you could not write SET SomeCol IS NULL).

Jonathan Leffler
  • 941
  • 8
  • 15
  • 1
    The question was more about the = NULL compared to IS NULL. Setting a value to NULL is ANSI standard. As you mention, all DBMSs allow that, but that's not what the question is about. – Richard Aug 20 '11 at 19:34
  • @Jonathan +1 for showing up just how inconsistent things are when it comes to null and because to my mind you answered the question "Is there a DBMS that allows the "= NULL" syntax?" – Jack Douglas Aug 23 '11 at 19:27