SQL Mastery: 3-Valued Logic, Aggregates & Correlated Subqueries

Most students know basic SELECT * FROM Table WHERE id = 1. But competitive examinations and production database engines test corner cases: what happens when NULLs appear in subqueries, how outer joins interact with aggregate filters, and how correlated subqueries execute.

SQL operates under Three-Valued Logic (3VL) with truth values TRUE, FALSE, and UNKNOWN. Missing data (NULL) is not zero or an empty string—it represents unknown or inapplicable information. Failing to understand 3VL leads to silent data bugs in production banking systems and lost marks in GATE exams.

Any comparison with NULL (even NULL = NULL) evaluates to UNKNOWN, never TRUE.

Truth Tables with UNKNOWN:

• AND: TRUE AND UNKNOWN = UNKNOWN, FALSE AND UNKNOWN = FALSE.

• OR: TRUE OR UNKNOWN = TRUE, FALSE OR UNKNOWN = UNKNOWN.

• NOT: NOT UNKNOWN = UNKNOWN.

The Infamous 'NOT IN' with NULL Trap:

Consider table R with values {1, NULL}. What does 'SELECT * FROM S WHERE x NOT IN (SELECT a FROM R)' return?

x NOT IN (1, NULL) expands logically to: (x ≠ 1) AND (x ≠ NULL).

Because (x ≠ NULL) always evaluates to UNKNOWN, the expression becomes: (x ≠ 1) AND UNKNOWN.

• If x = 1: FALSE AND UNKNOWN = FALSE.

• If x = 2: TRUE AND UNKNOWN = UNKNOWN.

In SQL, a WHERE clause only keeps rows that evaluate strictly to TRUE! Because the result is either FALSE or UNKNOWN for every possible value of x, the query returns EMPTY (0 rows)!

The Production Solution: Always use NOT EXISTS instead of NOT IN when subquery columns can contain NULLs.

SQL is written in one order but executed by the database engine in a strictly defined logical pipeline:

1. FROM / JOIN -> Identify data sources & compute Cartesian cross-products

2. WHERE -> Filter individual rows before grouping

3. GROUP BY -> Partition rows into buckets by group keys

4. HAVING -> Filter aggregated groups (can use COUNT, SUM, AVG)

5. SELECT -> Compute output projections & expressions

6. DISTINCT -> Eliminate duplicate output tuples

7. ORDER BY -> Sort final output rows (can use aliases defined in SELECT)

8. LIMIT / OFFSET -> Slice final row window

Key Rule: You can never use a column alias defined in SELECT inside the WHERE clause because WHERE executes before SELECT!