what does "delete from table where NULL = NULL" means?

Question

What does delete from table where NULL = NULL mean?

It means that you're on a job interview. – P Shved Jul 09 '10 at 15:43 — P Shved, Jul 09 '10 at 15:43

score 37 · Answer 1 · answered Jul 08 '10 at 06:37

37

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.

answered Jul 08 '10 at 06:37

Coxy

8,844
4
39
62

8

+1 for NULL != NULL. A bit more explanation, since I like this distinction. In database terms, NULL can mean a lack of information -- i.e. not provided, for whatever reason -- or unknown information, but you don't know which. Therefore, no two NULLs are alike. – George Marian Jul 08 '10 at 06:43
3

Right, and two NULLs are also not unalike. If you don't know the values, you can't say for sure whether they're the same or whether they're different. Hence any comparison yields *unknown* which is not *false* but it's definitely not *true* either. – Bill Karwin Jul 08 '10 at 07:03
5

+1 But with one caveat. It is so only in standard SQL and not all RDBMS provide the same standard dialect of SQL. I didn't post it as an answer, because the question is tagged _mysql_, but in MS SQL Server if you first issue `set ansi_nulls off` then `delete from table when null = null` will delete all rows because with this switch `null = null` becomes true. – Tomek Szpakowicz Jul 08 '10 at 07:23

score 16 · Answer 2 · answered Jul 08 '10 at 06:39

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.

+1 for the guessing about the generated query - which was the 1st thing that came on my mind when I read the question. — Fabricio Araujo, Dec 21 '10 at 20:59

score 8 · Answer 3 · answered Jul 08 '10 at 07:04

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.

score 5 · Answer 4 · answered Jul 08 '10 at 14:10

5

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

answered Jul 08 '10 at 14:10

Star

49
1

score 3 · Answer 5 · answered Jul 08 '10 at 06:38

3

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

DELETE FROM TABLE WHERE 0

answered Jul 08 '10 at 06:38

Konerak

39,272
12
98
118

score 3 · Answer 6 · edited Oct 24 '14 at 18:18

3

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

edited Oct 24 '14 at 18:18

Eric Leschinski

146,994
96
417
335

answered Jul 08 '10 at 07:24

Sam Saffron

128,308
78
326
506

1

isn't it a bit confusing to state that null is considered false (since NOT null would then have to be true, which isn't the case)? – potatopeelings Jul 08 '10 at 07:44
yes its confusing, but im specifically talking about conditions here. but the whole null thing is super confusing. – Sam Saffron Jul 08 '10 at 07:46
null is not considered false, but also it's not considered true. WHERE checks if expression evaluates to true. – Michał Piaskowski Jul 08 '10 at 08:33
Null represents the fact that you don't know a value. You can't say that something you don't know equals another thing that you don't know. Likewise, you can't say that that those two things don't equal one another. Strictly speaking NULL = NULL should evaluate to NULL because you don't know whether those two things are equal or not. False however is good enough for most cases. – Alex Humphrey Jul 08 '10 at 09:49

score 2 · Answer 7 · answered Jul 08 '10 at 06:37

2

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.

answered Jul 08 '10 at 06:37

Alex Humphrey

6,099
4
26
41

score -2 · Answer 8 · edited Oct 24 '14 at 18:39

-2

Not a single row is affected by this SQL command.

edited Oct 24 '14 at 18:39

Gary

13,303
18
49
71

answered Jul 08 '10 at 08:59

Sandeep Patle

15
3

what does "delete from table where NULL = NULL" means?

8 Answers8

Linked