Three Valued Logic: TRUE, FALSE and UNKNOWN

Concept

In most programming languages a condition is true or false. In SQL it can also be UNKNOWN, which is what every comparison involving NULL produces. WHERE, HAVING and ON keep a row only when the result is TRUE - UNKNOWN is discarded exactly like FALSE.

Syntax

-- The two rows worth memorising
FALSE AND UNKNOWN  =  FALSE     -- FALSE wins for AND
TRUE  OR  UNKNOWN  =  TRUE      -- TRUE  wins for OR
NOT   UNKNOWN      =  UNKNOWN   -- NOT never resolves an unknown

Example

-- Nikhil has dept_id = NULL. Watch him disappear from both sides.
SELECT first_name FROM employees WHERE dept_id = 10;
SELECT first_name FROM employees WHERE dept_id <> 10;

-- Neither query returns Nikhil. The two results do not add up to the table.
SELECT COUNT(*) AS total FROM employees;                       -- 8
SELECT COUNT(*) AS in_ten     FROM employees WHERE dept_id = 10;
SELECT COUNT(*) AS not_in_ten FROM employees WHERE dept_id <> 10;

-- To cover every row you have to say so explicitly
SELECT COUNT(*) AS everyone_else
FROM   employees
WHERE  dept_id <> 10 OR dept_id IS NULL;

Explanation

This is the practical consequence of three valued logic: a condition and its negation do not partition the table. Rows with NULL fall through both. Every "everything except X" filter on a nullable column needs an explicit OR col IS NULL.

The NOT IN trap, explained

-- Which departments have no employees at all?
SELECT name FROM departments
WHERE  id NOT IN (SELECT dept_id FROM employees);
-- Returns NOTHING, even though department 40 (Research) is empty.

The subquery returns 10, 20, 30 and NULL, because Nikhil has no department. 40 NOT IN (10, 20, 30, NULL) expands to 40 <> 10 AND 40 <> 20 AND 40 <> 30 AND 40 <> NULL. The last comparison is UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN, so no row ever qualifies.

-- Fix 1: exclude NULLs from the subquery
SELECT name FROM departments
WHERE  id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);

-- Fix 2, and the one to prefer: NOT EXISTS is NULL safe by construction
SELECT d.name
FROM   departments d
WHERE  NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

NULL safe comparison

-- MySQL / MariaDB
SELECT NULL <=> NULL AS null_safe_equal;   -- 1 (true)

-- Standard SQL, PostgreSQL, SQL Server 2022+
-- SELECT (NULL IS NOT DISTINCT FROM NULL) AS null_safe_equal;   -- true

Important rules

• A row survives a filter only when the condition is TRUE.
• FALSE AND UNKNOWN is FALSE; TRUE OR UNKNOWN is TRUE. Everything else touching UNKNOWN stays UNKNOWN.
• NOT UNKNOWN is UNKNOWN, so negation never brings NULL rows back.
• CHECK constraints are the exception that catches people out: they accept a row when the condition is UNKNOWN. CHECK (salary > 0) permits a NULL salary.
• EXISTS and NOT EXISTS return only TRUE or FALSE - never UNKNOWN - which is why they are the safe choice.

Common mistakes

• Assuming WHERE x = 1 and WHERE x <> 1 together cover the table.
• Using NOT IN with a nullable subquery column - the single most common NULL bug in production SQL.
• Expecting CHECK (col > 0) to also enforce NOT NULL.
• Writing WHERE NOT (status = 'active') and quietly dropping every NULL status row.

Best practices

• Prefer NOT EXISTS to NOT IN whenever a subquery is involved.
• Add OR col IS NULL - deliberately and visibly - to every negative filter on a nullable column.
• Reduce the problem at the source: make columns NOT NULL where unknown is not a real state.
• Pair every CHECK with NOT NULL when the column must always have a value.

Practice

1. On the sample data, find every employee not in department 10, including those with no department.
2. Rewrite the empty department query with NOT EXISTS and confirm Research appears.
3. What does SELECT COUNT(*) FROM employees WHERE dept_id NOT IN (10, NULL); return, and why?