Selecting Rows with NULL Values

Go Up to Selecting Multiple Rows

Any column can have NULL values, except those defined with the NOT NULL or UNIQUE integrity constraints. Rather than store a value for the column, InterBase sets a flag indicating the column has no assigned value.

Use IS NULL in a WHERE clause search condition to query for NULL values. For example, some rows in the DEPARTMENT table do not have a value for the BUDGET column. Departments with no stored budget have the NULL value flag set for that column. The following cursor declaration retrieves rows for departments without budgets for possible update:

EXEC SQL
DECLARE NO_BUDGET CURSOR FOR
SELECT DEPARTMENT, BUDGET
FROM DEPARTMENT
WHERE BUDGET IS NULL
FOR UPDATE OF BUDGET;

Note: To determine if a column has a NULL value, use an indicator variable. For more information about indicator variables, see Retrieving Indicator Status.

A direct query on a column containing a NULL value returns zero for numbers, blanks for characters, and 17 November 1858 for dates. For example, the following cursor declaration retrieves all department budgets, even those with NULL values, which are reported as zero:

EXEC SQL
DECLARE ALL_BUDGETS CURSOR FOR
SELECT DEPARTMENT, BUDGET
FROM DEPARTMENT
ORDER BY BUDGET DESCENDING;

Topics

• Limitations on NULL Values