28  SQL NULL

• NULL is used for unknown or missing values.

• null means “unknown” so that any comparison or operation results in unknown.

• for mathematical operations and logical operations, you may think null as \(\infty\) in calculus.

• \(\infty\) \(!=\) \(\infty\)

• NULL \(!=\) NULL

• 3 + NULL = NULL

• ‘string value’ + NULL = NULL

• for comparisons, null comparison always return false.

  • NULL = NULL returns false
  • NULL IS NULL returns true

sqlite

select null = null;

select null is null;

oracle

SELECT  * FROM dual WHERE NULL = NULL;


SELECT  * FROM dual WHERE NULL IS NULL;

28.1 NULL Working 1

28.1.1 NULL NOT operation

p	NOT p
True	False
False	True
Unknown	Unknown

28.1.2 NULL 3 valued logic

p	q	p OR q	p AND q	p = q
True	True	True	True	True
True	False	True	False	False
True	Unknown	True	Unknown	Unknown
False	True	True	False	False
False	False	False	False	True
False	Unknown	Unknown	False	Unknown
Unknown	True	True	Unknown	Unknown
Unknown	False	Unknown	False	Unknown
Unknown	Unknown	Unknown	Unknown	Unknown

28.1.3 sqlite null handling

see below table from sqlite documentation NULL Handling in SQLite Versus Other Database Engines