NULL

If a table is created and only partially filled with data, the fields that contain no data are considered to be null, meaning their values are unknown.

A null value 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 a table. A null value occurs where no value was entered, or where the value was set to null. It prints and displays as a single hyphen (-) by default.

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

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

To select rows that have a null value in a column, enter:

WHERE columnname IS NULL

Examples

To select everyone who does not receive a commission, use a query like the following query:

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

To select everyone whose commission is zero, use a query like the following query:

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

To select everyone who does get a commission, use a query like the following query:

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