2.7f

NMET Special Values

• Special value indicating unknown or inapplicable data.

• Different from zero for numeric data types or blanks for character data types.

• When arithmetic or comparison operators have one or more null operands, the result is null.

• If a WHERE clause evaluates to null for values in a row, that row is not selected.

Constraints

NOT NULL Constraint

• Prevents a column from having a NULL value.

• Insertion or updates to rows with NOT NULL constraints are automatically rejected if null values are referenced.

SQL Null Value Selection

• To select rows with null values, the syntax can be as follows:

  • SELECT * FROM Employee WHERE salary IS NULL.

• This query will never return any rows since the WHERE clause evaluates to null for rows with NULL salary.

• Using IS NULL and IS NOT NULL:

  • IS NULL returns TRUE when the value in a column is null.

  • IS NOT NULL returns TRUE when the value in a column is not null.

NULL Logic Table

• Logical combinations involving NULL values:

  • x AND y results in NULL if either x or y is NULL; otherwise, it follows standard boolean logic.

  • x OR y results in TRUE if either x or y is TRUE; if both are NULL, it yields NULL.

  • NOT x results in NULL if x is NULL.