NULL

If a table is created and only partially filled with data, the locations in which nothing is entered contain a code word called NULL, meaning value unknown. NULL is not the same as any of these values:

• A numerical value of zero
• A character string of all blanks
• A character string of length zero
• The character string NULL (of length 4)

Each of these values can be entered in a row and column of some table. NULL occurs where no value was entered, or where the value was specifically set to NULL. It prints and displays as a single hyphen (-).

• This is correct: WHERE columnname IS NULL
• This is incorrect: WHERE columnname = ' '

The VALUE scalar function can be used to change how a null is printed and displayed. See String functions.

To select rows that have NULL in a column, enter:

WHERE columnname IS NULL

Examples:

• Select everyone who does not receive a commission:

  SELECT ID, NAME
  FROM Q.STAFF
  WHERE COMM IS NULL

• Select everyone whose commission is zero:

  SELECT ID, NAME
  FROM Q.STAFF
  WHERE COMM = 0

  0 (zero) is not the same as NULL. No row in the sample table satisfies this condition.

• Select everyone who does get a commission:

  SELECT ID, NAME
  FROM Q.STAFF
  WHERE COMM IS NOT NULL