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


NULL = NULL 

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

Example:


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


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 

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
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap