29 SQL NULL Functions

30 SQL NULL functions SQL Standard

coalesce(X,Y,…)

The coalesce() function returns its first non-NULL argument.

If all arguments are null, returns NULL.
It requires at least two arguments.

coalesce by itself satisfy a lot of use cases involving null. coalesce is always better choice for null handling since coalesce is supported by a lot of different databases like sqlite, oracle, sql server, postgres, mysql ….

working with NULL - COALESCE example

    SELECT 
    COALESCE(FirstName + ' ' + SecondName + ' ' + LastName
    ,FirstName + ' ' + LastName
     ) AS FullName FROM COMMON.PERSON;

nullif(X,Y)

null if function returns first argument ,X, if two arguments are different.
it returns null if the arguments are same.

case

SQL Case

30.1 SQL NULL functions sqlite

See SQL Standard functions. There no extra sqlite null handling functions apart from SQL Standard coalesce and nullif.

31 SQL NULL functions Oracle

COALESCE

See, SQL standard.

NULLIF

See, SQL standard.

NVL(x,y)

NVL function allows you to replace a null value with default value. If first argument is null, second argument of NVL is returned. Coalesce function is more powerful than nvl and coalesce also works in other databases. Use coalesce, do not use NVL.

SELECT NVL(NULL,'DEFAUL VALUE') FROM DUAL;

NVL2(x,y,z)

NVL2 works with 3 parameters. If x is not null, it returns y. If x is null, it returns z.

This function could also be replaced with coalesce easily. Do not use it.

SELECT NVL2(NULL,'Y','Z') FROM DUAL;

SELECT NVL2('NOT NULL','Y','Z') FROM DUAL;

See following article null-related-functions for more obscure null related functions in Oracle.

31.1 SQL NULL functions SQL Server

coalesce

see coalesce in sql server documentation.

ISNULL(column/variable,value) if column/variable is null, then return value. Coalesce is almost always better than this function.

see isnull in sql server documentation.

NULLIF(column/variable,value) if column/variable is equals to value, then returns NULL.

SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different;

see [nullif in sql server documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql?view=sql-server-ver16.