Differnece Between NULL and empty in SQL

Differnece Between NULL and empty in SQL

To indicate that a value has deliberately not been specified, use the NULL keyword. NULL is the preferred way in SQL to indicate that a data value is nonexistent.

The empty string ('') is not the same thing as NULL. An empty string is a specified string. It is the shortest possible string, one that contains no characters. An empty string is represented internally by the non-display character $CHAR(0). Thus NULL and the empty string are different in nature, though in many instances the results of their use are identical. The empty string should be avoided in SQL coding. However, because many SQL operations delete trailing blank spaces, a data value that contains only whitespace characters (spaces and tabs) may be handled as an empty string. If an empty string is explicitly specified as a column default value, this value is represented by the non-display character $CHAR(0).

The SQL empty string, like all SQL strings, can also be represented with double quote characters (""), but this usage should be avoided because of potential conflict with SQL delimited identifiers.

You can convert an empty string to a NULL by using the ASCII function, as shown in the following example:

SELECT DISTINCT NULL AS NullVal,
{fn ASCII('')} AS EmpStrVal
FROM Sample.Person

 

NULL Processing

The NOT NULL data constraint requires that a field must receive a data value; specifying NULL rather than a value is not permitted. This constraint does not prevent the use of an empty string value. For further details, refer to the CREATE TABLE command.

The NULL predicate in the WHERE or HAVING clause of a SELECT statement selects NULL values; it does not select empty string values.

The IFNULL function selects NULL values, it does not select empty string values.

The COALESCE function selects the first non-NULL value from supplied data. It treats empty string values as non-NULL.

When the CONCAT function or the concatenate operator (||) concatenate a string and a NULL, the result is NULL. This is shown in the following example:

SELECT DISTINCT {fn CONCAT('fred',NULL)} AS FuncCat,
'fred'||NULL AS OpCat
FROM Sample.Person

 

The AVG, COUNT, MAX, MIN, and SUM aggregate functions ignore NULL values when performing their operations. (COUNT * counts all rows, because there cannot be a record with NULL values for all fields.) The DISTINCT keyword of the SELECT statement includes NULL in its operation; if there are NULL values for the specified field, DISTINCT returns one NULL row.

The AVG, COUNT, and MIN, aggregate functions are affected by empty string values. The MIN function considers an empty string to be the minimum value, even when there are rows that have a value of zero. The MAX and SUM aggregate functions are not affected by empty string values.

NULL Arithmetic

Any SQL arithmetic operation that has NULL as an operand returns a value of NULL. Thus, 7+NULL=NULL. This includes the binary operations addition (+), subtraction (-), multiplication (*), division (/), integer division (\), and modulo (#), and the unary sign operators plus (+) and minus (-).

An empty string specified in an arithmetic operation is treated as a value of 0 (zero).

The Length of NULL

Within SQL, the length of a NULL is undefined (it returns <null>); the length of an empty string, however, is defined as length zero, as shown in the following example:

SELECT DISTINCT CHAR_LENGTH(NULL) AS NullLen,
CHAR_LENGTH('') AS EmpStrLen
FROM Sample.Person

 

However, certain Caché extensions to standard SQL treat the length of NULL and the empty string differently. The $LENGTH function returns a length of 0 for a NULL, and a length of 1 for an empty string value. This functionality is compatible with Caché ObjectScript:

SELECT DISTINCT $LENGTH(NULL) AS NullLen,
$LENGTH('') AS EmpStrLen
FROM Sample.Person

 

Another place where the internal representation of these values is significant is in the %STRING, %SQLSTRING and %SQLUPPER functions, which append a blank space to a value. Since a NULL truly has no value, appending a blank to it creates a string of length 1. But an empty string does have a character value, so appending a blank to it creates a string of length 2. This is shown in the following example:

SELECT DISTINCT CHAR_LENGTH(%STRING(NULL)) AS NullLen,
CHAR_LENGTH(%STRING('')) AS EmpStrLen
FROM Sample.Person