10

What is the difference between the following 2 queries?

DELETE FROM experience WHERE end IS NOT NULL;

And

DELETE FROM experience WHERE NOT( end=NULL);

First query was accepted as a correct answer but second was not.

Peter O.
  • 32,158
  • 14
  • 82
  • 96
vyom bhatia
  • 115
  • 5
  • It also depends on how the null value is treated. See http://stackoverflow.com/questions/9766717/in-sql-server-what-does-set-ansi-nulls-on-mean – Richard Vivian Mar 18 '16 at 05:08

5 Answers5

7

NULLs are a bit weird. A NULL is never equal to anything including another NULL. Further, any boolean operation against a NULL returns NULL.

The expression end IS NOT NULL will evaluate false if end is NULL, and true if end is not NULL.

The expression NOT( end=NULL) will actually always evaluate to NULL because (end = NULL) equals NULL and NOT (NULL) also equals NULL. More to the point in a WHERE clause, it will never evaluate true.

Darwin von Corax
  • 5,201
  • 3
  • 17
  • 28
5

NULL values are treated differently from other values.

NULL is used as a placeholder for unknown or inapplicable values. It is not possible to test for NULL values with comparison operators, such as =, <, or <>

You will have to use the IS NULL and IS NOT NULL operators instead.

Please refer below link for more details.

http://www.w3schools.com/sql/sql_null_values.asp

Pushpendra Pal
  • 640
  • 6
  • 19
1

Comparing a variable to null (i.e. field = NULL) is the same as assigning an unknown value to "field." IS NOT NULL checks to see if the field is null. The second NOT (end=NULL) is assigning a value of "Unknown" to field and then NOTing the result. You should not assign a variable to an unknown value.

LuvnJesus
  • 631
  • 4
  • 9
1

You should not use "NOT" alone. It should be followed by "IS". So if you want the second query to work then use - DELETE FROM experience WHERE IS NOT( end=NULL);

vivkv
  • 931
  • 2
  • 14
  • 29
1

An expression with NULL in it can never be evaluated as true except if combined with IS (but see further). As stated in the MySql docs:

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.

Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.

Exceptions to the Rule

Not all expressions with NULL as argument evaluate to NULL. Apart from NULL IS NULL, also the following will yield a truthy value:

NULL <=> NULL,
COALESCE(NULL, true),
IFNULL(NULL, true),
ISNULL(NULL),
1 IN (1, NULL),
INTERVAL(1, 0, 2, NULL)
CASE 1 WHEN 1 THEN TRUE WHEN NULL THEN false END

The reason that the last three do not yield NULL is that these expressions perform some form of short-circuit evaluation. So if during the evaluation from left-to-right the outcome is clear, the rest of the expression (including the NULL) is not evaluated.

But all of the following will be NULL:

NULL = NULL
CASE NULL WHEN NULL THEN TRUE END,
'test' || NULL,
GREATEST(1, NULL),
1 NOT IN (2, NULL)

Note how in the last two expressions, short-circuiting is not a possibility: all arguments must be evaluated, at least until a NULL is found.

ANSI_NULLS Setting

There is an ANSI_NULLS setting that influences the above behaviour when set to OFF. It is ON by default, and it should in fact not be altered. As stated in the docs:

Important

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

So, I don't believe it is worth discussing this setting further: don't touch it.

Community
  • 1
  • 1
trincot
  • 317,000
  • 35
  • 244
  • 286