Selecting Rows with NULL Values

From InterBase

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;

Limitations on NULL Values

Because InterBase treats NULL values as non-values, the following limitations on NULL values in queries should be noted:

  • Rows with NULL values are sorted after all other rows.
  • NULL values are skipped by all aggregate operations, except for COUNT(*).
  • NULL values cannot be elicited by a negated test in a search condition.
  • NULL values cannot satisfy a join condition.

NULL values can be tested in comparisons. If a value on either side of a comparison operator is NULL, the result of the comparison is Unknown.

For the Boolean operators (NOT, AND, and OR), the following considerations are made:

  • NULL values with NOT always returns Unknown.
  • NULL values with AND return Unknown unless one operand for AND is false. In this latter case, False is returned.
  • NULL values with OR return Unknown unless one operand for OR is true. In this latter case, True is returned.

For information about defining alternate NULL values, see the Data Definition Guide.

Advance To:

Retrieved from "http://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=Selecting_Rows_with_NULL_Values&oldid=31191"