SQL - Null

Data System Architecture

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





Discover More
Jdbc Class Architecture
JDBC - Null

in Jdbc (ie SQL NULL) See java/sql/ResultSetwasNull function of a resultSet
Card Puncher Data Processing
Oracle Database - Null value (Missing Value)

SQL Null in the Oracle context. By default all columns allow NULL. Nulls are stored in the database and require 1 byte to store the length of the column (zero). Null is not aggregated and selected on...
Data System Architecture
SQL - (Query) Predicate

in SQL. A predicate acts as a filter that filters a certain number of rows from a row set. You have two principal types of predicates: join predicates or filter predicates Predicates are used in:...
Data System Architecture
SQL - Boolean

The Boolean data type in SQL is a Three-valued logic (3VL) boolean with: true false and unknown (Stored with a SQL Null) In SQL, the unkown is the output of a comparisons with the NULL value where...
Card Puncher Data Processing
SQL Plus - Null

The value to show when a NULL value is found in the database can be set: globally through the NULL system variable or by column through the NULL option
Data System Architecture
What are the LAG and LEAD SQL functions ?

lag and lead are Sql window functions that provide access to a row at a given offset prior to/after the current row position.



Share this page:
Follow us:
Task Runner