What is NULL and how is it different from empty string?

NULL = absence of value (unknown/missing)

Empty string = known value that is empty

Zero = known numeric value

Examples:

• NULL: Phone number not provided

• Empty string: Name field left blank

• Zero: Account balance is $0

Key difference: NULL ≠ NULL (unknown ≠ unknown)

Empty string = empty string (known = known)

Use IS NULL to check for NULL, not = NULL.

Explain three-valued logic

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

NULL comparisons return UNKNOWN:

• NULL = NULL → UNKNOWN

• NULL != 5 → UNKNOWN

• NULL > 10 → UNKNOWN

WHERE clause behavior:

• TRUE → row included

• FALSE → row excluded

• UNKNOWN → row excluded

This is why "WHERE column = NULL" never works. Use "WHERE column IS NULL" instead.

How do aggregates handle NULL?

Aggregates ignore NULL values:

COUNT(*): Includes NULLs (counts all rows)

COUNT(column): Excludes NULLs (counts non-NULL values)

SUM/AVG/MIN/MAX: Ignore NULLs

Example:

values: 10, NULL, 20, NULL

COUNT(*) = 4

COUNT(column) = 2

SUM = 30

AVG = 15 (30/2, not 30/4)

If all values NULL, result is NULL.

What is COALESCE and when to use it?

COALESCE returns first non-NULL value:

COALESCE(val1, val2, val3, default)

Use cases:

• Display fallback: COALESCE(phone, email, 'No contact')

• NULL-safe calculations: salary + COALESCE(bonus, 0)