5

Could you help me, I need to understand about the difference between

select * from table where field <> NULL;

and

select * from table where field is not NULL;

and see

SELECT COUNT(*) where (1 = null) -- return 0
SELECT COUNT(*) where (1 <> null) -- return 0
SELECT COUNT(*) where (1 is not  null) -- return 1
SELECT COUNT(*) where (null = null) -- return 0
SELECT COUNT(*) where (null <> null) -- return 0
SELECT COUNT(*) where (null is null) -- return 1
SELECT COUNT(*) where (null is not  null) -- return 0

Why is null = null false?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
VhsPiceros
  • 410
  • 1
  • 8
  • 17
  • Short answer: **Comparison operators with `NULL` always produce `NULL`. Period.** – Lukasz Szozda Oct 07 '15 at 00:39
  • thanks @lad2025 , but why (null = null) is false? – VhsPiceros Oct 07 '15 at 01:01
  • Think about this like `unknown = unknown`? Or `Infinity = Infinity`? `NULL` is rather concept not specific value – Lukasz Szozda Oct 07 '15 at 01:04
  • Yeah, that is the final question, I supposed that null = null always is true, almost in all language i used. but i understand this for SQL, I can't compare null with null because SQL compare the concept and not the value,right? – VhsPiceros Oct 07 '15 at 01:10
  • 2
    The point is that `NULL` is SQL has to meaning 1) missing value 2) unknown value. So `UNKNOWN` = `UNKNOWN` cannot be true, at least not always. – Lukasz Szozda Oct 07 '15 at 01:15
  • @lad2025 Ok, perfect, It's clear for my now.. thanks for your time and share your knowledge ;) – VhsPiceros Oct 07 '15 at 01:18

1 Answers1

8

1) First question about difference IS NULL vs = NULL:

Comparison operators like (=, <>, <, >, ...) with NULL always produce NULL. Use IS NULL/IS NOT NULL instead.

2) Second question "why (null = null) is false":

From SQL and the Snare of Three-Valued Logic:

One kind of NULL marks values which are:

missing because the value is unknown

and the other kind marks values that are missing because the attribute is missing.

When you try to compare NULL you actualy do something like

UNKNOWN = UNKNOWN

This is of course unknown.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 3
    Unknown=unknown isn't false, it's unknown. –  Oct 07 '15 at 06:25
  • No, it's treated like unknown, which only looks like it's treated as false in the trivial cases. If it were consistently treated as false inside `WHERE` clauses, then `SELECT 1 WHERE NOT (NULL = NULL)` would return 1. In fact, it returns nothing. –  Oct 07 '15 at 07:53