SQL select not a string doesn't return NULL va

问题:

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 don't have foo in the name column.

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

DROP TABLE #temps

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

回答1:

My solution would be

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

回答2:

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

Others have answered what to do about it. As to why it is like that, null represents an unknown value. The value for column name in row 3 could be foo. We don't know that because the value is unknown.

The where clause have to evaluate to true to return a row. name <> 'foo' is not true and it is not false it is unknown.

回答3:

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