Three-Valued Logic: The Foundation of NULL Behavior

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- Sample data
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10,2)
);
 
INSERT INTO products VALUES
(1, 'Widget', 10.00),
(2, 'Gadget', NULL),
(3, 'Thing', 20.00),
(4, 'Stuff', NULL);
 
-- Query 1: Find products with price > 15
SELECT * FROM products WHERE price > 15;
-- Result: Only row 3 (Thing, 20.00)
-- 
-- Evaluation per row:
-- Row 1: 10.00 > 15 = FALSE → excluded
-- Row 2: NULL > 15 = UNKNOWN → excluded
-- Row 3: 20.00 > 15 = TRUE → INCLUDED
-- Row 4: NULL > 15 = UNKNOWN → excluded
 
-- Query 2: Find products with price <= 15
SELECT * FROM products WHERE price <= 15;
-- Result: Only row 1 (Widget, 10.00)
--
-- Note: Rows 2 and 4 are STILL excluded!
-- They have UNKNOWN price, so both > and <= comparisons are UNKNOWN
 
-- Query 3: What about NOT (price > 15)?
SELECT * FROM products WHERE NOT (price > 15);
-- Result: Only row 1 (Widget, 10.00)
--
-- Evaluation per row:
-- Row 1: NOT FALSE = TRUE → INCLUDED
-- Row 2: NOT UNKNOWN = UNKNOWN → excluded
-- Row 3: NOT TRUE = FALSE → excluded
-- Row 4: NOT UNKNOWN = UNKNOWN → excluded
 
-- The NULL rows are excluded from BOTH positive and negative searches!
-- This is the "disappearing rows" phenomenon.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- To include NULL rows, handle them explicitly:
 
-- Find products with price > 15 OR unknown price
SELECT * FROM products 
WHERE price > 15 OR price IS NULL;
-- Returns rows 2, 3, 4
 
-- Find products with price <= 15 OR unknown price
SELECT * FROM products 
WHERE price <= 15 OR price IS NULL;
-- Returns rows 1, 2, 4
 
-- Now the union of these two queries covers ALL rows
-- (which is what we'd expect mathematically)
 
-- Practical example: Find products NOT in a specific price range
-- Include products with unknown price (they might be in range)
SELECT * FROM products
WHERE price < 5 
   OR price > 25 
   OR price IS NULL;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

-- Complex expression evaluation example
-- Given: A = TRUE, B = UNKNOWN, C = FALSE
 
-- Expression: A AND B OR C
-- Step 1: A AND B = TRUE AND UNKNOWN = UNKNOWN
-- Step 2: UNKNOWN OR C = UNKNOWN OR FALSE = UNKNOWN
-- Result: UNKNOWN
 
-- Expression: A OR B AND C  
-- Step 1: B AND C = UNKNOWN AND FALSE = FALSE (FALSE wins)
-- Step 2: A OR FALSE = TRUE OR FALSE = TRUE
-- Result: TRUE
 
-- Practical SQL example
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);
 
INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 80000),
(2, 'Bob', 'Sales', NULL),
(3, 'Carol', NULL, 90000),
(4, 'Dave', NULL, NULL);
 
-- Find: Engineering employees OR employees with salary > 85000
SELECT * FROM employees
WHERE department = 'Engineering' OR salary > 85000;
 
-- Row-by-row evaluation:
-- Row 1: 'Engineering' = 'Engineering' OR 80000 > 85000
--        = TRUE OR FALSE = TRUE → included
--
-- Row 2: 'Sales' = 'Engineering' OR NULL > 85000
--        = FALSE OR UNKNOWN = UNKNOWN → excluded
--
-- Row 3: NULL = 'Engineering' OR 90000 > 85000  
--        = UNKNOWN OR TRUE = TRUE → included
--
-- Row 4: NULL = 'Engineering' OR NULL > 85000
--        = UNKNOWN OR UNKNOWN = UNKNOWN → excluded
 
-- Result: Rows 1 and 3 (Alice and Carol)
 
-- Now with AND:
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 70000;
 
-- Row-by-row evaluation:
-- Row 1: 'Engineering' = 'Engineering' AND 80000 > 70000
--        = TRUE AND TRUE = TRUE → included
--
-- Row 2: 'Sales' = 'Engineering' AND NULL > 70000
--        = FALSE AND UNKNOWN = FALSE → excluded
--
-- Row 3: NULL = 'Engineering' AND 90000 > 70000
--        = UNKNOWN AND TRUE = UNKNOWN → excluded
--
-- Row 4: NULL = 'Engineering' AND NULL > 70000
--        = UNKNOWN AND UNKNOWN = UNKNOWN → excluded
 
-- Result: Only Row 1 (Alice)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- Short-circuit example (behavior varies by DBMS)
 
-- Potentially safe because IS NOT NULL comes first
SELECT * FROM products
WHERE quantity IS NOT NULL 
  AND (total_value / quantity) > 100;
 
-- But the optimizer might evaluate conditions in any order!
-- If quantity IS NULL and division is evaluated first,
-- you could get a division-by-zero or NULL error.
 
-- Safer approach: Use CASE to guarantee evaluation order
SELECT * FROM products
WHERE CASE 
    WHEN quantity IS NULL THEN FALSE
    WHEN quantity = 0 THEN FALSE
    ELSE (total_value / quantity) > 100
END = TRUE;
 
-- Or NULLIF / COALESCE to protect against the issue
SELECT * FROM products
WHERE total_value / NULLIF(quantity, 0) > 100;
-- NULLIF returns NULL if quantity = 0, and NULL/0 = NULL
-- Then NULL > 100 = UNKNOWN, row excluded safely
 
-- Some databases document short-circuit behavior:
-- PostgreSQL: Generally left-to-right in AND/OR, but not guaranteed
-- Oracle: No guaranteed order of evaluation
-- SQL Server: May evaluate in any order for optimization
 
-- Best practice: Don't rely on short-circuit evaluation.
-- Write conditions that are safe regardless of evaluation order.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

-- CASE WHEN with UNKNOWN
SELECT 
    product_name,
    price,
    CASE 
        WHEN price > 100 THEN 'Premium'
        WHEN price > 50 THEN 'Standard'
        WHEN price > 0 THEN 'Budget'
        -- What about NULL prices?
        -- All comparisons are UNKNOWN, so no WHEN matches
        -- Falls through to ELSE (or NULL if no ELSE)
        ELSE 'Unknown'
    END AS tier
FROM products;
 
-- CHECK constraint with UNKNOWN
CREATE TABLE salaries (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),
    CONSTRAINT chk_positive CHECK (amount > 0)
);
 
INSERT INTO salaries VALUES (1, 50000);  -- OK: 50000 > 0 = TRUE
INSERT INTO salaries VALUES (2, NULL);   -- OK! NULL > 0 = UNKNOWN, passes
INSERT INTO salaries VALUES (3, -100);   -- FAILS: -100 > 0 = FALSE
 
-- This is why CHECK constraints use "NOT FALSE" semantics,
-- not "IS TRUE" semantics. To reject NULLs, add explicit check:
CREATE TABLE salaries_strict (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),
    CONSTRAINT chk_positive CHECK (amount > 0 AND amount IS NOT NULL)
    -- Or simply: amount DECIMAL(10,2) NOT NULL CHECK (amount > 0)
);
 
-- JOIN ON with UNKNOWN
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- If o.customer_id is NULL:
-- NULL = c.id → UNKNOWN for all customers → no match
 
-- IN with NULL in the list
SELECT * FROM products WHERE category_id IN (1, 2, NULL);
-- For category_id = 1: TRUE (matches 1)
-- For category_id = 3: FALSE OR FALSE OR UNKNOWN = UNKNOWN
-- But UNKNOWN doesn't affect matching; only TRUE matches are returned
-- So this works like IN (1, 2) for practical purposes
 
-- NOT IN with NULL in the list (THE TRAP)
SELECT * FROM products WHERE category_id NOT IN (1, 2, NULL);
-- For category_id = 3:
-- NOT (FALSE OR FALSE OR UNKNOWN) = NOT UNKNOWN = UNKNOWN
-- Row is excluded! This returns ZERO rows (or very few).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

-- Debugging technique: Expose the boolean evaluation
 
-- Original query returning unexpected results
SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;
 
-- Debug query: Show what each condition evaluates to
SELECT 
    id,
    name,
    department,
    salary,
    -- Expose individual condition results
    CASE WHEN department = 'Sales' THEN 'TRUE'
         WHEN department <> 'Sales' THEN 'FALSE'
         ELSE 'UNKNOWN' END AS dept_check,
    CASE WHEN salary > 50000 THEN 'TRUE'
         WHEN salary <= 50000 THEN 'FALSE'
         ELSE 'UNKNOWN' END AS salary_check,
    -- Expose combined result using 3VL AND rules
    CASE 
        WHEN department = 'Sales' AND salary > 50000 THEN 'TRUE'
        WHEN department <> 'Sales' OR salary <= 50000 THEN 'FALSE'
        ELSE 'UNKNOWN' 
    END AS combined
FROM employees;
 
-- Quick NULL detection in all columns
SELECT 
    'department' AS column_name,
    COUNT(*) AS total_rows,
    COUNT(department) AS non_null_count,
    COUNT(*) - COUNT(department) AS null_count
FROM employees
UNION ALL
SELECT 
    'salary',
    COUNT(*),
    COUNT(salary),
    COUNT(*) - COUNT(salary)
FROM employees;
 
-- Debugging NOT IN issue
-- Instead of:
SELECT * FROM products WHERE category_id NOT IN (SELECT id FROM excluded);
 
-- Debug by seeing what the subquery returns:
SELECT id, 
       CASE WHEN id IS NULL THEN 'NULL FOUND!' ELSE 'OK' END
FROM excluded;
 
-- If NULLs exist, that's your problem. Fix with:
SELECT * FROM products 
WHERE category_id NOT IN (
    SELECT id FROM excluded WHERE id IS NOT NULL
);
-- OR better:
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM excluded e WHERE e.id = p.category_id
);