Is NULL not allowed is SQL delete statement (for Derby)?

Question

Hello Does anyone know why the following query fails in Derby?

delete from MyTable
where
((null = null) or (col1 = null)) OR
((102 = null) or (col2 = 102)))

I get the following error:

Error: Syntax error: Encountered "null" at line 3, column 3.
SQLState:  42X01
ErrorCode: -1

The sql is being generated in a java program based on SQL I've written into an iBatis ORM layer config. The prepared statement is as follows:

delete from MyTable
where
((? = null) or (col1 = ?)) OR
((? = null) or (col2 = ?)))

As you can see what I'm trying to say is that if the parameter is not null then test the column value against the parameter

Answer 1

You do not write SQL like that. Not sure how it is for Derby but for most it is:

delete from MyTable 
where 
((col1 IS null)) OR 
((102 IS null) or (col2 = 102))) 

Answer 2

I believe what you want is:

DELETE FROM `name_of_table` WHERE `name_of_column` IS NULL

SQL has this really weird property where you can use =value with most values, except NULL.

Answer 3

The first part of the where clause suggests it should match every row (if you expect null = null to be true, which it generally wouldn't be in SQL) - do you really want to delete the whole table's contents? Likewise 102 = null is a pretty bizarre kind of comparison, unless you've somehow got a column called 102 (which I'd at least hope is prohibited).

What are you trying to actually match? As others have said, you should use "is null" to compare a value against null - but it sounds like your query has rather bigger problems. If you could tell us what you're trying to achieve, we may be able to help you write the query better.

Answer 4

In SQL, any operator where one of the arguments is NULL evaluates to NULL. So 1 + NULL is NULL, MAX(NULL) is NULL, and so on. Most importantly, in your case, X = NULL always evaluates to NULL, even if X is NULL itself (that is, NULL = NULL evaluates to NULL not true).

The exact error you get is that the keyword NULL is not actually valid on the left-hand side of a comparison. But even if you fixed that, you'd still find that none of your rows ever match. As others have said, the correct comparison to use when trying to determine whether something is null is IS NULL, as in X IS NULL.