NULL in WHERE clause

Question

I have the following prepared statement. All is good...

$stmt=$conn->prepare('UPDATE t1 SET c1=? WHERE c2=? AND c3!=?'); $stmt->execute(array(1,2,3));

But now I wish to use the same prepared statement, but include NULL values in the WHERE clause.

$stmt->execute(array(1,NULL,NULL));

Will this resolve to UPDATE t1 SET c1=1 WHERE c2=NULL AND c3!=NULL? Isn't the proper query UPDATE t1 SET c1=1 WHERE c2 IS NULL AND c3 IS NOT NULL? How can I have a single prepared statement which allows both NULL and not NULL values in the WHERE clause?

possible duplicate of Execute PDO with an array containing null values — Rahil Wazir
– Rahil Wazir, Commented Jun 25, 2014 at 19:30
@RahilWazir Referenced "possible duplicate" is for inserting NULL. INSERTs or SETs do not cause a problem, only the WHERE clause. — user1032531
– user1032531, Commented Jun 25, 2014 at 19:33

O. Jones · Accepted Answer · 2014-06-25 19:45:25Z

1

You asked if a SQL query containing

 WHERE col = ?

will automatically be converted to

 WHERE col IS NULL

by binding a NULL value to the parameter.

The answer is no. If a column has a NULL value, it can never compare equal to anything.

You need a different prepared query for that.

edited Jun 25, 2014 at 19:45

answered Jun 25, 2014 at 19:39

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1032531 Over a year ago

Thanks Ollie. Do I understand you in that I cannot use the same prepared statement when having NULL and non-NULL in the WHERE clause?

Andrew Over a year ago

Correct. If you think NULL might come into play, you need to handle that in your query, which is why nullable columns shouldn't be overused.

user1032531 Over a year ago

Thanks again Ollie. Well, that is a bummer!

Collectives™ on Stack Overflow

NULL in WHERE clause

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related