NULL Values and Three-Valued Logic in SQL

 

NULL Values and Three-Valued Logic in SQL

1. What is NULL in SQL?

In SQL, NULL represents the absence of a value.

It does not mean zero, empty string, or false.

SQL uses NULL to represent missing or inapplicable information, and it does not distinguish between the reasons for missing data.

---

2. Meanings of NULL (Conceptual Interpretation)

Although SQL treats all NULLs the same, conceptually a NULL may mean:

(a) Unknown Value

• The value exists but is not known.

• Example:

  • Date of birth of an employee is unknown → Bdate = NULL

  • Home phone is NULL because it is not known whether the employee has one.

(b) Unavailable or Withheld Value

• The value exists but is intentionally hidden.

• Example:

  • An employee has a phone number but does not want it disclosed.

(c) Not Applicable Value

• The attribute does not apply to that tuple.

• Example:

  • LastCollegeDegree = NULL for a person with no college education.

👉 Important point:
SQL does not store which meaning applies. All are simply stored as NULL.

---

3. Comparing NULL Values

In SQL:

• A NULL value is not equal to anything, not even another NULL.

• Any comparison involving NULL (using =, <, >, etc.) yields:

UNKNOWN

Example:

Salary = 50000        → UNKNOWN if Salary is NULL
Salary > 40000        → UNKNOWN if Salary is NULL
NULL = NULL           → UNKNOWN

---

4. Why Three-Valued Logic?

Because comparisons involving NULL do not evaluate to TRUE or FALSE, SQL uses:

Three-Valued Logic

• TRUE

• FALSE

• UNKNOWN

This logic applies to conditions in the WHERE clause.

---

5. Logical Operators in Three-Valued Logic

(a) AND Operator

AND	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

Example:

(FALSE AND UNKNOWN) → FALSE
(TRUE AND UNKNOWN)  → UNKNOWN

---

(b) OR Operator

OR	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

Example:

(FALSE OR UNKNOWN) → UNKNOWN
(TRUE OR UNKNOWN)  → TRUE

---

(c) NOT Operator

Operand	Result
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

Example:

NOT UNKNOWN → UNKNOWN

---

6. Effect of Three-Valued Logic on SELECT Queries

In SQL:

Only rows where the WHERE condition evaluates to TRUE are selected.

• Rows evaluating to FALSE → rejected

• Rows evaluating to UNKNOWN → also rejected

Example:

SELECT *
FROM EMPLOYEE
WHERE Salary > 50000;

• Employees with Salary = NULL → condition evaluates to UNKNOWN
  ❌ These rows are not included

---

7. Checking for NULL in SQL

❌ Incorrect way:

WHERE Super_ssn = NULL

✔ Correct way:

WHERE Super_ssn IS NULL

or

WHERE Super_ssn IS NOT NULL

Reason:

• SQL treats each NULL as distinct

• Equality (=) cannot be used with NULL

---

8. Example from Elmasri & Navathe

Query: Employees without supervisors

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn IS NULL;

Explanation:

• Super_ssn IS NULL explicitly checks for missing supervisor

• Employees at the top of the hierarchy (e.g., manager or CEO) are returned

---

9. NULL and JOIN Conditions

In INNER JOINs:

• Tuples with NULL in join attributes are excluded

• Because join condition evaluates to UNKNOWN

Example:

EMPLOYEE ⋈ EMPLOYEE
ON EMPLOYEE.Super_ssn = EMPLOYEE.Ssn

• If Super_ssn is NULL → no match → tuple excluded

📌 Exception:
OUTER JOINs may retain such tuples and pad missing values with NULLs.

---

10. Key Exam-Ready Points

• NULL represents missing or inapplicable data

• SQL uses three-valued logic: TRUE, FALSE, UNKNOWN

• Any comparison with NULL → UNKNOWN

• WHERE clause selects only TRUE

• Use IS NULL / IS NOT NULL, not = NULL

• INNER JOIN removes tuples with NULL join attributes

• OUTER JOIN can preserve them