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

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 

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

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


Powered by ComboStrap