CODEWITHSUNDEEP
sql
Matthew Layton
Matthew Layton

Reputation: 42229

Why is NULL considered a special case in SQL?

Assume the following database table:

Accounts

id externalId firstName lastName
4c8e49a6-b148-4125-9352-c2effda744b8 null Alan Turing
9bb67137-07cf-413b-8f7e-d710a9c52c19 null Bill Gates
7510fe8e-a976-4258-bf5a-a314373f6743 'abc' Charles Babbage
62222be0-5e85-4333-9683-7b2de03073c5 'xyz' Dennis Richie

The following query returns the following results:

SELECT TOP (1000) [id]
      ,[externalId]
      ,[firstName]
      ,[lastName]
  FROM [ExampleDb].[dbo].[Accounts]
WHERE [externalId] != 'abc'
id externalId firstName lastName
62222be0-5e85-4333-9683-7b2de03073c5 'xyz' Dennis Richie

Why does this query not return rows where externalId is null (null != 'abc') ?

Upvotes: 1

Views: 113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This is how NULL is defined. Period. It means "unknown value" semantically, not "missing value". So NULL != 'abc' returns NULL because the value is not known. And WHERE only returns expressions that explicitly evaluate to "true".

Your real question is why SQL Server doesn't support a NULL safe comparison operator. So the same Standard that defines the behavior of NULL also defines IS DISTINCT FROM to do what you want:

where externalId is distinct from 'abc'

The question is why this is not supported in SQL Server.

Upvotes: 3

Related Questions