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
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:
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:
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 (-).
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:
However, certain Caché extensions to standard SQL treat the length of NULL and the empty string differently. The
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é
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