SQL - Null

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

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

Every predicate expression with a NULL comparison returns:

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

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.

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

Example:

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

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

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

Als this operations returns null

1 * NULL 
NULL / 0

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

All null string operation returns null

'foo' || NULL

NULLs sort differently on different engines.

Different database engines interpret NULLs in a UNIQUE constraint differently.

To have a consistent sort order, you should avoid using NULLs in UNIQUE column.