3.3.4 · NULL in SQL: three-valued logic, IS NULL, comparisons

0 · Recall Warm-up

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

How many rows does SELECT dept FROM STUDENT return, and with DISTINCT? from Lesson 3.3.3
What is NULL = NULL? from Lesson 2.1.2
What is ¬(p ∧ q) by De Morgan? from Lesson 0.2.3

1 · The Hook

A new student arrives: Esha (roll 5, CS), with no CGPA yet. From this lesson on, STUDENT has five rows; Esha's cgpa is NULL and she has no enrolments. SQL now has to combine TRUE, FALSE and UNKNOWN inside AND, OR and NOT. GATE tests these truth tables directly and through NOT IN traps.

roll	name	dept	cgpa
1	Asha	CS	9.1
2	Bhavin	EE	7.8
3	Chirag	CS	8.4
4	Diya	ME	6.9
5	Esha	CS	NULL

STUDENT from Lesson 3.3.4 on

2 · Predict First

How many rows: SELECT name FROM STUDENT WHERE dept = 'CS' OR cgpa > 8; and … WHERE dept = 'CS' AND cgpa > 8;?

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

3 · Core Concept

Predict First — the answer

OR: Asha, Chirag, Esha (dept = 'CS' is TRUE, and TRUE OR UNKNOWN = TRUE) → 3. AND: Asha, Chirag; for Esha, TRUE AND UNKNOWN = UNKNOWN → dropped → 2.

Three-valued logic (T, F, U)

AND	T	U	F
T	T	U	F
U	U	U	F
F	F	F	F

OR	T	U	F
T	T	T	T
U	T	U	U
F	T	U	F

NOT T = F, NOT F = T, NOT U = U.

Shortcuts: FALSE wins AND; TRUE wins OR; otherwise UNKNOWN spreads. Think of U as "maybe": T AND maybe = maybe; F AND maybe = F.

Where TRUE is required

WHERE and HAVING keep a row only if the condition is TRUE.
CHECK rejects only FALSE (Lesson 3.3.1).

IS NULL and IS NOT NULL

cgpa IS NULL is never UNKNOWN: TRUE for Esha, FALSE for others. Never write cgpa = NULL (always UNKNOWN, selects nothing).

The NOT IN trap

x NOT IN (a, b, NULL) means x ≠ a AND x ≠ b AND x ≠ NULL. The last part is UNKNOWN, so the whole condition is never TRUE: the query returns no rows. x IN (a, b, NULL) still finds x = a or x = b.

NULL in other places

DISTINCT and GROUP BY treat all NULLs as one value (two NULL rows count as duplicates).
ORDER BY: NULLs sort first or last depending on the system (SQLite and MySQL: first in ASC; PostgreSQL and Oracle: last in ASC).
Arithmetic: NULL + 1 = NULL.
Aggregates ignore NULLs (next lesson).

4 · Visual

Esha: dept='CS' -> TRUE      cgpa > 8 -> UNKNOWN

TRUE  OR  UNKNOWN = TRUE      -> Esha appears in the OR query
TRUE  AND UNKNOWN = UNKNOWN   -> Esha is dropped by the AND query
NOT (cgpa > 8)    = UNKNOWN   -> Esha also dropped by "NOT (cgpa > 8)"
cgpa IS NULL      = TRUE      -> the only way to catch her

5 · Worked Examples

Example 1 Easy

Evaluate: (T AND U), (F AND U), (T OR U), (F OR U), NOT U.

Solution

U, F, T, U, U.

Example 2 Medium

How many rows: SELECT * FROM STUDENT WHERE NOT (cgpa > 8);?

Solution

cgpa > 8: Asha, Chirag TRUE; Bhavin, Diya FALSE; Esha UNKNOWN. NOT gives TRUE for Bhavin and Diya only: 2 rows.

Example 3 GATE

SELECT name FROM STUDENT WHERE roll NOT IN (2, 4, NULL); How many rows? And with IN (2, 4, NULL)?

Solution

NOT IN: for every row, "roll ≠ NULL" is UNKNOWN, so the AND is never TRUE: 0 rows. IN: rolls 2 and 4 match: 2 rows (Bhavin, Diya).

6 · GATE Traps

NOT UNKNOWN is UNKNOWN; negating a condition does not catch NULL rows.
= NULL never selects anything; use IS NULL.
NOT IN with a NULL in the list returns no rows.
F AND U = F; T OR U = T. Do not turn every U into U.
DISTINCT treats NULLs as equal; comparisons do not.

7 · Memory Hook

"False kills AND, True saves OR, Unknown is contagious otherwise." And: "One NULL in a NOT IN list poisons the whole well."

8 · Connect the Dots

Lesson 2.1.2 introduced UNKNOWN; this lesson gave its full logic inside SQL. Next: aggregates and grouping (COUNT, SUM, AVG, GROUP BY, HAVING), where NULLs are quietly ignored, which changes averages and counts.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Truth tables

UNKNOWN OR FALSE is:

(A) FALSE
(B) NULL
(C) UNKNOWN
(D) TRUE

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

How many rows: SELECT * FROM STUDENT WHERE cgpa > 7 OR dept = 'ME';

Numerical answer: ________

Q3 · NAT · 1 mark(s) — NOT IN

How many rows: SELECT * FROM STUDENT WHERE dept NOT IN ('EE', NULL);

Numerical answer: ________

Q4 · MCQ · 1 mark(s) — IS NULL

Which condition selects exactly Esha's row?

(A) cgpa <> cgpa
(B) NOT (cgpa > 0)
(C) cgpa IS NULL
(D) cgpa = NULL

Q5 · NAT · 2 mark(s) — Count rows

How many rows: SELECT * FROM STUDENT WHERE NOT (dept = 'CS' AND cgpa < 9);

Numerical answer: ________

10 · Rule Card

AND: F dominates; OR: T dominates; else U. NOT U = U.
WHERE/HAVING keep only TRUE; CHECK rejects only FALSE.
Test NULL with IS NULL / IS NOT NULL; = NULL is always UNKNOWN.
x NOT IN (…, NULL) is never TRUE.
DISTINCT/GROUP BY treat NULLs as one value; ORDER BY NULL position is system-dependent.

11 · Concept Ledger

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

three-valued logicAND/OR/NOT truth tables with UNKNOWNIS NULLIS NOT NULLNOT IN with NULL trapNULL in DISTINCT and ORDER BY

12 · Answer Vault

Reveal answers when you’re ready

Recall Warm-up

4; 3.
UNKNOWN.
¬p ∨ ¬q.