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.