YCH Dev-SQL Three-Valued Logic (3VL) vs. C# Two-Valued Logic — Understanding the NULL Comparison Pitfall

SQL Three-Valued Logic (3VL) vs. C# Two-Valued Logic — Understanding the NULL Comparison Pitfall

Author: ych

## 1. Overview and Background

Developers transitioning from high-level languages like C# or Java to SQL often encounter unexpected behavior when handling `NULL` values.

The most common manifestation of this issue occurs when evaluating inequality (e.g., checking if a value is "not equal to '1'"). SQL filters out rows where the column is `NULL`, whereas equivalent code in C# would evaluate `null != "1"` as true and process those records.

This document analyzes the logical differences between these environments and provides standard solutions alongside runnable T-SQL case studies.

---

## 2. Core Concepts: Two-Valued Logic vs. Three-Valued Logic

### 2.1 C# Two-Valued Logic (2VL)
Boolean evaluations in C# are binary. An expression must resolve to either `true` or `false`.
* In C#, `null` indicates the absence of a value or object reference.
* When evaluating `flag != "1"`, if `flag` is `null`, C# confirms that `null` is indeed distinct from the string `"1"`. The expression evaluates to `true`.

### 2.2 SQL Three-Valued Logic (3VL)
According to the ANSI SQL standard, `NULL` represents **Unknown** or **Missing Data**. Because the value is missing, the database engine cannot determine if it is equal or unequal to a specified value. 
Consequently, SQL uses a third truth value: **`UNKNOWN`**.

#### Comparison Truth Table (using the `!=` operator):
| Operand A | Operator | Operand B | Evaluation Result | SQL Condition Behavior |
| :--- | :---: | :--- | :--- | :--- |
| `'0'` | `!=` | `'1'` | **`TRUE`** | **Passes condition** (Row is returned / block executes) |
| `'1'` | `!=` | `'1'` | **`FALSE`** | **Fails condition** (Row is filtered out / block skipped) |
| `NULL` | `!=` | `'1'` | **`UNKNOWN`** | **Fails condition** (Row is filtered out / block skipped) |

> **Key Rule:**
> In SQL, `IF` statements and `WHERE` clauses only execute or return records when the condition evaluates strictly to **`TRUE`**. Conditions evaluating to `FALSE` or `UNKNOWN` are bypassed.

---

## 3. Standard Solutions

To ensure that columns containing `NULL` are handled when checking for inequality, use one of the following approaches.

### Solution A: Explicit `IS NULL` Evaluation (Recommended)
This is the standard and most readable approach. It is SARGable (Search Argument Able) and does not hinder index performance.
```sql
WHERE Column != '1' OR Column IS NULL
```

### Solution B: Using `ISNULL()` or `COALESCE()` Default Values
This approach wraps the column in a function to substitute `NULL` with a default value (such as an empty string `''`) before comparison.
```sql
WHERE ISNULL(Column, '') != '1'
```
*Note: Applying functions to indexed columns in large tables can prevent the database engine from utilizing indexes, resulting in full table scans. Solution A is generally preferred for `WHERE` clause filtering.*

---

## 4. Case Studies

Below are three T-SQL case studies illustrating how this logic applies to variables, filtering queries, and procedural validation checks.

### Case Study 1: Inventory Posting Verification (Procedural Variables)
**Scenario:** An application scans inventory barcodes. If a barcode's posting status (`InStoreFlag`) in the stock table (`MPRPD`) is not `'1'` (meaning it is either not in stock or is in an undefined `NULL` state), the system must raise an error.

#### Simulated Data Setup:
```sql
-- Declare a table variable to simulate inventory
DECLARE @Inventory TABLE (
    Barcode VARCHAR(20),
    InStoreFlag VARCHAR(1) -- '1' = Posted, '0' = Pending, NULL = Unknown/Error
);

INSERT INTO @Inventory (Barcode, InStoreFlag) VALUES 
('BC001', '1'),     -- Posted
('BC002', '0'),     -- Pending
('BC003', NULL);    -- Unknown State (NULL)
```

#### The Incorrect Approach (Bypasses NULL states):
```sql
-- Simulating scanning BC003
DECLARE @ScanBarcode VARCHAR(20) = 'BC003';
DECLARE @CurrentFlag VARCHAR(1);

SELECT @CurrentFlag = InStoreFlag FROM @Inventory WHERE Barcode = @ScanBarcode;

-- @CurrentFlag is retrieved as NULL
IF @CurrentFlag != '1'
BEGIN
    PRINT 'Validation Succeeded: Barcode is not posted.';
END
ELSE
BEGIN
    PRINT 'Bug: Validation bypassed because NULL != ''1'' evaluated to UNKNOWN.';
END
-- Result: "Bug: Validation bypassed..."
```

#### The Correct Approach (Secures the validation):
```sql
DECLARE @ScanBarcode VARCHAR(20) = 'BC003';
DECLARE @CurrentFlag VARCHAR(1);

SELECT @CurrentFlag = InStoreFlag FROM @Inventory WHERE Barcode = @ScanBarcode;

-- Explicitly handling the NULL state
IF @CurrentFlag != '1' OR @CurrentFlag IS NULL
BEGIN
    PRINT 'Validation Succeeded: Barcode is not posted (NULL handled).';
END
-- Result: "Validation Succeeded..."
```

---

### Case Study 2: User Status Filtering (WHERE Clause Query)
**Scenario:** A query needs to extract all users who are not currently suspended. 
* Column `IsSuspended`: `'1'` indicates suspended, `'0'` indicates active, and `NULL` indicates newly registered users (who are active by default).

#### Simulated Data Setup:
```sql
DECLARE @Users TABLE (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    IsSuspended VARCHAR(1)
);

INSERT INTO @Users VALUES 
(1, 'John', '0'),    -- Active
(2, 'Alex', '1'),    -- Suspended
(3, 'Emma', NULL);   -- New User (Active)
```

#### Incorrect Query (Excludes new users):
```sql
SELECT * FROM @Users WHERE IsSuspended != '1';
```
**Results:**
| UserID | Username | IsSuspended |
| :--- | :--- | :--- |
| 1 | John | '0' |

*(Emma is missing because `NULL != '1'` evaluates to `UNKNOWN`)*

#### Correct Query (Includes all active users):
```sql
SELECT * FROM @Users WHERE IsSuspended != '1' OR IsSuspended IS NULL;
```
**Results:**
| UserID | Username | IsSuspended |
| :--- | :--- | :--- |
| 1 | John | '0' |
| 3 | Emma | NULL |

---

### Case Study 3: First-In-First-Out (FIFO) Validation
**Scenario:** Before issuing material, the system must check if there is an older batch that is unlocked.
* Column `IsLockedByBill`: Contains a document ID if locked, or `NULL`/`''` (empty string) if unlocked.
* Rule: If an older unlocked batch exists, raise a warning.

#### Simulated Data Setup:
```sql
DECLARE @Stock TABLE (
    BatchNo VARCHAR(20),
    CreateDate DATETIME,
    IsLockedByBill VARCHAR(50) -- Document ID, NULL or '' means unlocked
);

INSERT INTO @Stock VALUES 
('BATCH01', '2026-01-01', NULL),          -- Older batch, unlocked
('BATCH02', '2026-01-02', 'BILL888'),     -- Older batch, locked
('BATCH03', '2026-01-03', '');            -- Current scanning batch
```

#### Incorrect FIFO Validation:
This check uses `IsLockedByBill != ''` which incorrectly evaluates to `UNKNOWN` for `BATCH01` due to the `NULL` value.
```sql
-- Checking if there is an older, unlocked batch
IF EXISTS (
    SELECT 1 FROM @Stock 
    WHERE CreateDate < '2026-01-03' 
      AND IsLockedByBill != '' -- Incorrectly assumes NULL != '' is True
)
BEGIN
    PRINT 'FIFO Violation: An older unlocked batch exists.';
END
ELSE
BEGIN
    PRINT 'Bug: Bypassed. FIFO check did not detect BATCH01.';
END
```

#### Correct FIFO Validation:
```sql
IF EXISTS (
    SELECT 1 FROM @Stock 
    WHERE CreateDate < '2026-01-03' 
      -- Correctly identifies unlocked states (empty string or NULL)
      AND (IsLockedByBill IS NULL OR IsLockedByBill = '') 
)
BEGIN
    PRINT 'FIFO Violation: BATCH01 is older and unlocked.';
END
```

---

## 5. Summary and Best Practices

To prevent logic gaps when writing SQL queries or stored procedures:
1. Always assume any nullable column evaluated with standard comparison operators (`=`, `!=`, `<`, `>`) will return `UNKNOWN` if a `NULL` is encountered.
2. Pair inequalities with an explicit `OR Column IS NULL` check rather than assuming the inequality catches empty values.
3. Establish code review practices that audit comparisons against nullable columns.

SQL Three-Valued Logic (3VL) vs. C# Two-Valued Logic — Understanding the NULL Comparison Pitfall

Comments Section