How do you get SQL to learn WHERE column = NULL?

Question

In microsoft SQL, I have the following statement (approximately):

UPDATE sometable SET somecolumn = @somevalue WHERE somecolumn = NULL; 

@somevalue set earlier in the script.

This works fine, and SQL says that zero lines affect me. There are five lines: somecolumn - NULL. What am I doing wrong?

Answer 1

Instead, you should use IS NULL to check for the NULL value in the column.

  UPDATE sometable SET somecolumn = @somevalue WHERE somecolumn IS NULL; 

Answer 2

Try NULL

 UPDATE sometable SET somecolumn = @somevalue WHERE somecolumn IS NULL; 

Source: http://blogs.msdn.com/b/sqlclr/archive/2005/06/21/431329.aspx

Answer 3

Joe is right. Nothing can be equal null. It can only be null, therefore, UPDATE mytable SET mycolumn = @value WHERE anotherColumn IS NULL.

Answer 4

ANSI SQL defines NULL as nothing else — not even another instance of NULL. The canonical way around this is to use IS NULL and IS NOT NULL .

There is also an MS SQL Server SET ANSI_NULLS . Turning this option off causes WHERE x = NULL do exactly what you expect. It will also, however, include any NULL values ​​in a query of type WHERE x <> 'abc' - which may not be what you expect. This parameter is specific to the connection, so changing it for your connection will not affect others. You can also set the default setting at the database level. When creating a stored procedure, the parameter is recorded at creation time - not the execution time.

Another trick is building a query like WHERE ISNULL(x, '') = ISNULL(@x, '') . I don’t think it is SARGable, therefore the performance is not as good as WHERE (x IS NULL AND @x IS NULL) OR (x = @x) , but it is much more pleasant to write and build dynamically.

Oh - and since we're talking about ANSI SQL. The ANSI SQL ISNULL is COALESCE .