Joe Celko's SQL for Smarties, 3rd Edition

6.10. A Note on Multiple NULL Values

In a discussion on CompuServe in July 1996, Carl C. Federl came up with an interesting idea for multiple missing value tokens in a database.

If you program in embedded SQL, you are used to having to work with an INDICATOR column. This column is used to pass information to the host program, mostly about the NULL or NOT NULL status of the SQL column in the database. What the host program does with the information is up to the programmer. So why not extend this concept a bit and provide an indicator column in SQL? Let’s work out a simple example:

CREATE TABLE Bob (keycol INTEGER NOT NULL PRIMARY KEY, valcol INTEGER NOT NULL, multi_indicator INTEGER NOT NULL CHECK (multi_indicator IN (0, -- Known value 1, -- ...