Browse Prior Art Database

Extension of the IS Predicate in SQL

IP.com Disclosure Number: IPCOM000122636D
Original Publication Date: 1991-Dec-01
Included in the Prior Art Database: 2005-Apr-04
Document File: 1 page(s) / 28K

Publishing Venue

IBM

Related People

Hoffman, RD: AUTHOR

Abstract

Disclosed is a method which extends the SQL IS predicate to handle non-null values.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 100% of the total text.

Extension of the IS Predicate in SQL

      Disclosed is a method which extends the SQL IS predicate
to handle non-null values.

      In SQL, data elements which have no defined value may take on
the NULL value.  However, due to the requirements of three-valued
logic, it is not possible to directly compare a value with NULL using
the equals ('=') operator, since "X = NULL" is always undefined.  The
IS predicate has been introduced for this purpose: one codes "WHERE X
IS NULL" instead of "WHERE X = NULL" to ensure that the predicate
will be true, not undefined, when X is undefined.

      Consider a test of a column against a host variable which may
(but need not) represent the NULL value.  To simply code "WHERE X =
:hv" will not work if the host variable is NULL, so one must code:
           WHERE X = :hv OR (X IS NULL AND :hv IS NULL)

      This can be simplified if the IS predicate is extended to
accept values other than NULL as objects.  Define the predicate "x IS
y" to be true if either both x and y are NULL or if neither
is NULL and x = y.  This is completely compatible with the current
case, where y is restricted to be the keyword NULL.  Then the above
example can be rewritten:
                        WHERE X IS :hv

      The IS NOT predicate is similarly extended.