SQL select not a string doesn't return NULL value

Given the following table and data

CREATE TABLE #temps
(
    id int,
    name varchar(max)
)

INSERT INTO #temps VALUES (1, 'foo')
INSERT INTO #t开发者_运维百科emps 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.

---

My solution would be

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

---

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.

---

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