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
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.
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).
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
