Geek Answers Handbook

SQL select not a string does not return NULL

Given the following table and data

CREATE TABLE #temps ( id int, name varchar(max) ) INSERT INTO #temps VALUES (1, 'foo') INSERT INTO #temps VALUES (2, '') INSERT INTO #temps VALUES (3, NULL)

I want to select all rows that do not have foo in the name column.

 SELECT * FROM #temps WHERE name <> 'foo' DROP TABLE #temps

Why does this only return row # 2? The name in line # 3 is NOT foo and must be returned.

+4
source share
3 answers

My solution would be

 SELECT * FROM #temps WHERE ISNULL(name, '') <> 'foo'
+4
source

Why does this only return row # 2? the name on line # 3 is not foo and should.

Others said what to do about it. As to why this is so, null represents the value unknown . The value for the name column in row 3 may be foo . We do not know this, because the meaning is unknown.

The where clause must be evaluated to true to return a string. name <> 'foo' not true and it is not false , it is unknown.

+5
source

You need where Name <> 'foo' or Name is null . Null cannot be compared for equality.

+4
source

More articles:


All Articles