SQL - Null

1 - 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

3 - DataType

Because NULL does not have official data type, it leads to funny output.

Example in Oracle

create view nullview
select null "null" from dual;

describe nullview;

Name           Null?    Type
 -------------- -------------------------------------
 null                    VARCHAR2

4 - Operations

4.1 - 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

4.1.1 - Equality

By default NULL value not treated as equal with each other.

For instance, Postgres returns an error - Operator does not exist


BUT in set operation, such as UNION or INTERSECT NULLs are mostly treated as equal with each other. - 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

4.1.2 - Greater than, Lower Than


NULL <> n -- Return Null
NULL > n -- Return Null

4.1.3 - Logical Operator

NULL OR TRUE -- return true  
NULL OR FALSE -- return null

4.1.4 - Switch Statement

    ELSE 'NULL is Not NULL'
  END as "Is NULL null?"


Is NULL null?    

4.2 - Arithmetic

Als this operations returns null

1 * NULL 
NULL / 0

4.3 - 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

4.4 - String

All null string operation returns null

'foo' || NULL

4.5 - Sort

NULLs sort differently on different engines.

  • SQLite sorts NULL values first.
  • PostgreSQL and MySQL and most other database engiens sort NULL values last.

5 - 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.

6 - Documentation / Reference

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap