Tutorial :what does “delete from table where NULL = NULL” means?



Question:

What does delete from table where NULL = NULL mean?


Solution:1

That will delete nothing from the table. NULL does not equal NULL.

Now

delete from table where NULL is NULL

would delete all rows from the table.


Solution:2

It means don't delete anything, because NULL is never equal to anything. Or maybe it means "don't delete anything unless the user's DBMS really sucks, in which case delete it all out of spite".

Seriously though, that kind of construct usually comes about when a WHERE clause is procedurally generated -- rather than creating a special case for "do nothing", sometimes it's simpler just to generate a WHERE clause that causes the database to do nothing. I've usually seen "WHERE 0 = 1" though, which is less ambiguous.


Solution:3

In SQL, there are three logical values, namely TRUE, FALSE, and UNKNOWN. when we compare null to null, using null=null, the operation will return UNKNOWN. Moreover,In the WHERE clause all UNKNOWN values are filtered out.Hence the query does nothing.


Solution:4

It punishes people who have ANSI_NULLS set to off in their database :)


Solution:5

Since NULL does not equal NULL, this statement will do nothing. It equals:

DELETE FROM TABLE WHERE 0  


Solution:6

Recap:

mysql> select null = null, null <> null, null is null, null = 1, null <> 1;  +-------------+--------------+--------------+----------+-----------+  | null = null | null <> null | null is null | null = 1 | null <> 1 |  +-------------+--------------+--------------+----------+-----------+  |        NULL |         NULL |            1 |     NULL |      NULL |  +-------------+--------------+--------------+----------+-----------+  1 row in set (0.00 sec)      mysql> select count(*) from table;  +----------+  | count(*) |  +----------+  |     10   |  +----------+  1 row in set (0.33 sec)      mysql> select * from table where null;  Empty set (0.00 sec)  

Meaning, if a condition evaluates to null it is considered false by MySql so delete from table where NULL = NULL will in fact delete nothing.

NULL is a special beast as noted by Codd


Solution:7

I imagine it depends on the database, but to my knowledge, it shouldn't achieve anything, as NULL is never equal to NULL, at least in db theory.


Solution:8

Not a single row is affected by this SQL command.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »