SQL - Null
Table of Contents
About
NULL value in SQL represents missing data in SQL.
NULL has its own data domain and implements therefore its own behavior.
The most important one being that:
- An equality comparison of two NULL values will not return TRUE but NULL. Therefore NULL = NULL in a where clause is not TRUE and will then not return the record.
- Most of the data operations with NULL returns NULL (but not always)
- NULL values are not counted in a count operation
Articles Related
DataType
Because NULL does not have official data type, it leads to funny output.
Example in Oracle
create view nullview
as
select null "null" from dual;
describe nullview;
Name Null? Type
-------------- -------------------------------------
null VARCHAR2
Operations
Logical / Predicate
Every predicate expression with a NULL comparison returns:
- the SQL boolean with the NULL value (NOT true or false)
- or an error
To take null into account, you should use a COALESCE or NVL function that will transforms NULL into a value of the same data domain
Example for oracle:
( val1 + val2 ) <> 0;
should be rewritten as:
( nvl(val1,0) + nvl(val2,0) ) <> 0;
to take NULL value into account
Equality
By default NULL value not treated as equal with each other.
For instance, Postgres returns an error - Operator does not exist
NULL = NULL
Is NULL
To select records with NULL value, the expression IS NULL should be used.
To get all records where the year is NULL:
select * from table where year is null
Greater than, Lower Than
Example:
NULL <> n -- Return Null
NULL > n -- Return Null
Logical Operator
NULL OR TRUE -- return true
NULL OR FALSE -- return null
Switch Statement
SELECT
CASE NULL
WHEN NULL
THEN 'NULL is NULL'
ELSE 'NULL is Not NULL'
END as "Is NULL null?"
FROM
dual;
Result:
Is NULL null?
----------------
NULL is Not NULL
Arithmetic
Als this operations returns null
1 * NULL
NULL / 0
Count
Count from a column will not count the NULL value.
select count(NullColumn) from mytable;
If you mark your column with a NOT NULL constraint, a count can then use an index otherwise it will do a full scan
String
All null string operation returns null
'foo' || NULL
Sort
NULLs sort differently on different engines.
- SQLite sorts NULL values first.
- PostgreSQL and MySQL and most other database engiens sort NULL values last.
Unique Constraint
Different database engines interpret NULLs in a UNIQUE constraint differently.
- SQLite, PostgreSQL, Oracle, MySQL, and Firebird do it one way.
- Informix, DB2, and MS-SQL do it another.
To have a consistent sort order, you should avoid using NULLs in UNIQUE column.
Documentation / Reference
- See section 4.4.2 of the Iso SQL Specification - ISO/IEC 9075-1:2011