NULL: meaning and basic behaviour

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What is the degree of a relation, and what is its cardinality? from Lesson 2.1.1
2. Why can a relation not contain the same tuple twice? from Lesson 2.1.1
3. What does NULL mean in the ER model? from Lesson 1.1.2

1 · The Hook

Esha has just joined. Her CGPA does not exist yet. The table still needs a row for her. Writing 0 would say she failed everything; leaving the cell empty is not an option in a table. The relational model has one special marker for "there is no value here": NULL. It looks harmless, but it changes how comparisons work, and GATE uses that again and again.

2 · Predict First

Esha's cgpa is NULL. Is the condition cgpa > 8 true or false for her? Is cgpa = cgpa true for her?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

Neither true nor false. A comparison involving NULL gives a third truth value, UNKNOWN: we cannot say whether an unknown CGPA is above 8. Even cgpa = cgpa is UNKNOWN, because NULL is not a value that can be compared, not even with itself.

What NULL means

NULL marks a missing value. It has several possible meanings:

• value unknown (exists but not recorded): a student's phone number not yet collected;
• value not applicable: a day scholar's hostel_room;
• not known whether it exists: does this student have a passport?

The relational model uses one NULL for all of them. NULL is not 0, not the empty string, and not a member of the domain.

Basic behaviour

1. Arithmetic with NULL gives NULL: NULL + 1 = NULL, cgpa × 10 = NULL when cgpa is NULL.
2. Comparisons with NULL (=, <>, <, >, ≤, ≥) give UNKNOWN: NULL = 5 → UNKNOWN; NULL = NULL → UNKNOWN; NULL <> NULL → UNKNOWN.
3. A condition that is UNKNOWN does not select the tuple: queries keep only rows for which the condition is TRUE. Esha appears neither in "cgpa > 8" nor in "cgpa ≤ 8".
4. To test for NULL you use a special test, "A is null" (true or false, never unknown). SQL writes it IS NULL (Lesson 3.3.4).

Why it matters

The third row surprises people: a condition that "must" be true for every number still skips the NULL row. The full three-valued logic (how AND, OR, NOT combine TRUE, FALSE and UNKNOWN) comes in Lesson 3.3.4.

Where NULL is not allowed

Some attributes must never be NULL; the most important are the attributes that identify tuples (the primary key, Lesson 2.2.1). A designer can also declare any attribute NOT NULL.

Avoiding NULLs in design

Many NULLs in one column usually mean a design choice: hostel_room NULL for most students suggests a separate table ALLOTMENT(roll, room) with rows only for hostel students. Lesson 2.4.2 uses exactly this idea when mapping relationships.

4 · Visual

Comparison with NULL           Result
------------------------       -------
NULL  = 5                      UNKNOWN
NULL  = NULL                   UNKNOWN
NULL <> NULL                   UNKNOWN
NULL  + 1                      NULL      (arithmetic, not a truth value)
cgpa is null   (Esha)          TRUE
cgpa is null   (Asha 9.1)      FALSE

A query keeps a row only when its condition is TRUE.
FALSE and UNKNOWN rows are both left out.

5 · Worked Examples

Example 1 Easy

STUDENT has cgpa values 9.1, 7.8, NULL, 6.9. How many rows satisfy cgpa < 8? How many satisfy cgpa is null?

Solution

cgpa < 8: 7.8 and 6.9 → 2. cgpa is null: 1.

Example 2 Medium

Two students both have phone = NULL. Is "these two students have the same phone" TRUE?

Solution

No. NULL = NULL is UNKNOWN, not TRUE. Two missing values cannot be shown to be equal.

Example 3 GATE

A table has 10 rows; column x is NULL in 3 rows. Rows with x = 5: 4. How many rows satisfy x <> 5?

Solution

x <> 5 is TRUE only for the non-NULL rows that are not 5: 10 − 3 − 4 = 3. The 3 NULL rows give UNKNOWN and are left out.

8 · Connect the Dots

Lesson 2.1.1 defined relations with atomic values; this lesson added the one non-value, NULL, and its UNKNOWN comparisons. Next: how tuples are identified: superkeys, candidate keys and the primary key, which must never be NULL.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Comparisons

If x is NULL, the condition x = x evaluates to:

1. (A) UNKNOWN
2. (B) FALSE
3. (C) TRUE
4. (D) NULL as a value stored in the table

Q2 · NAT · 1 mark(s) — Counting rows

Column y holds 4, NULL, 7, 4, NULL, 9. How many rows satisfy y > 3?

Numerical answer: ________

Q3 · NAT · 2 mark(s) — Counting rows

Column y holds 4, NULL, 7, 4, NULL, 9. How many rows satisfy y = 4 OR y <> 4?

Numerical answer: ________

Q4 · MCQ · 1 mark(s) — Arithmetic

If cgpa is NULL, the expression cgpa × 10 is:

1. (A) an error
2. (B) 0
3. (C) NULL
4. (D) UNKNOWN

Q5 · MSQ · 2 mark(s) — Meaning

Which statements about NULL are correct?

1. (A) NULL can mean "value unknown".
2. (B) NULL is equal to the empty string.
3. (C) A query keeps a row only if its condition is TRUE.
4. (D) NULL can mean "not applicable".

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

NULL in relationsUNKNOWN (third truth value)NULL arithmetic"is null" testNOT NULL declaration