Boolean — SQL Data Types | OneNoughtOne

0/278

SQLData Types

SQL Data Types

LevelBeginner

Duration60 mins

TopicData Types

4 / 5

Boolean

The Simplest Type With Hidden Complexity

True or false—what could be simpler? Yet the Boolean type in SQL carries surprising complexity. Unlike programming languages where boolean is strictly binary, SQL's Boolean operates in a three-valued logic system where TRUE, FALSE, and NULL (unknown) interact in non-intuitive ways. The Boolean type appears simple but demands understanding of: Three-valued logic (TRUE, FALSE, UNKNOWN) NULL propagation in boolean expressions Vendor-specific implementations and storage Design patterns for nullable vs. non-nullable boolean columns This page provides complete coverage of the SQL Boolean type, ensuring you can use it correctly and avoid the subtle bugs that three-valued logic introduces.

What You Will Learn

By the end of this page, you will master SQL's three-valued Boolean logic, understand how NULL interacts with boolean expressions, navigate vendor-specific implementations, and apply best practices for boolean columns in production schemas.

Boolean Fundamentals

The SQL standard defines a BOOLEAN type with three possible values:

Value	Meaning	Behavior
TRUE	Affirmative	Condition is satisfied
FALSE	Negative	Condition is not satisfied
NULL	Unknown	Condition's truth cannot be determined

This three-valued logic (3VL) distinguishes SQL from most programming languages where boolean is strictly true/false. In SQL, "I don't know" is a valid answer. Why Three Values? Consider a query asking "Is this user verified?" If the is_verified column is NULL, the answer isn't false—it's unknown. Perhaps the verification check hasn't run yet, or the data wasn't collected. NULL represents this uncertainty.

boolean_basics.sql
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
-- Boolean column declarations
CREATE TABLE users (
    user_id         INTEGER PRIMARY KEY,
    username        VARCHAR(50) NOT NULL,
    email           VARCHAR(255) NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,    -- Required, defaults to true
    is_verified     BOOLEAN DEFAULT FALSE,            -- Nullable, defaults to false
    is_admin        BOOLEAN NOT NULL DEFAULT FALSE,   -- Required, defaults to false
    accepts_marketing BOOLEAN                         -- Nullable, no default (NULL)
);
 
-- Boolean literals
INSERT INTO users (user_id, username, email, is_active, is_verified, is_admin)
VALUES 
    (1, 'alice', 'alice@example.com', TRUE, TRUE, FALSE),
    (2, 'bob', 'bob@example.com', TRUE, FALSE, FALSE),
    (3, 'charlie', 'charlie@example.com', FALSE, NULL, FALSE);
 
-- Boolean comparisons
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_verified = FALSE;
SELECT * FROM users WHERE is_admin;              -- Shorthand for = TRUE
SELECT * FROM users WHERE NOT is_admin;          -- Shorthand for = FALSE
 
-- The NULL boolean case
SELECT * FROM users WHERE is_verified IS NULL;    -- User 3
SELECT * FROM users WHERE is_verified IS NOT NULL;
 
-- Common mistake: This returns NO ROWS for NULL values
SELECT * FROM users WHERE is_verified = NULL;     -- WRONG! Always false
-- NULL = NULL returns NULL (unknown), not TRUE
 
-- Correct NULL handling
SELECT * FROM users WHERE is_verified IS TRUE;    -- Only verified users
SELECT * FROM users WHERE is_verified IS FALSE;   -- Only explicitly unverified
SELECT * FROM users WHERE is_verified IS NOT TRUE; -- FALSE or NULL
SELECT * FROM users WHERE is_verified IS NOT FALSE; -- TRUE or NULL

The NULL = NULL Trap

In SQL, NULL = NULL returns NULL (unknown), not TRUE. Use IS NULL and IS NOT NULL for null checks. Similarly, WHERE is_verified = NULL matches no rows. This is one of the most common SQL mistakes.

Three-Valued Logic Deep Dive

SQL's three-valued logic affects all boolean operations. Understanding truth tables for AND, OR, and NOT with NULL is essential for correct query writing. AND Truth Table:

A	B	A AND B
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
TRUE	NULL	NULL
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE
FALSE	NULL	FALSE
NULL	TRUE	NULL
NULL	FALSE	FALSE
NULL	NULL	NULL

Key Insight: FALSE AND anything = FALSE (FALSE dominates). NULL AND TRUE = NULL (uncertainty remains).

OR Truth Table:

A	B	A OR B
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
TRUE	NULL	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE
FALSE	NULL	NULL
NULL	TRUE	TRUE
NULL	FALSE	NULL
NULL	NULL	NULL

Key Insight: TRUE OR anything = TRUE (TRUE dominates). NULL OR FALSE = NULL (uncertainty remains). NOT Truth Table:

A	NOT A
TRUE	FALSE
FALSE	TRUE
NULL	NULL

three_valued_logic.sql
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
65
66
67
68
69
70
71
72
73
74
75
76
-- Demonstrating three-valued logic
 
-- Setup test data
CREATE TABLE logic_test (
    id INT PRIMARY KEY,
    a BOOLEAN,
    b BOOLEAN
);
 
INSERT INTO logic_test VALUES
    (1, TRUE, TRUE),
    (2, TRUE, FALSE),
    (3, TRUE, NULL),
    (4, FALSE, TRUE),
    (5, FALSE, FALSE),
    (6, FALSE, NULL),
    (7, NULL, TRUE),
    (8, NULL, FALSE),
    (9, NULL, NULL);
 
-- AND operations
SELECT id, a, b, 
       a AND b AS and_result,
       CASE 
           WHEN a AND b IS TRUE THEN 'TRUE'
           WHEN a AND b IS FALSE THEN 'FALSE'
           ELSE 'NULL'
       END AS and_display
FROM logic_test;
 
-- Practical implication: Finding active and verified users
-- If is_verified is NULL, this returns no match
SELECT * FROM users
WHERE is_active AND is_verified;  -- NULL is_verified excluded
 
-- OR operations
SELECT id, a, b,
       a OR b AS or_result,
       CASE 
           WHEN a OR b IS TRUE THEN 'TRUE'
           WHEN a OR b IS FALSE THEN 'FALSE'
           ELSE 'NULL'
       END AS or_display
FROM logic_test;
 
-- Practical implication: Show if either condition met
SELECT * FROM users
WHERE is_admin OR is_verified;  -- TRUE if either is TRUE
                                 -- NULL propagates if one is NULL and other is FALSE
 
-- NOT operations
SELECT id, a, NOT a AS not_a_result
FROM logic_test;
 
-- The NOT NULL puzzle
-- NOT NULL returns NULL, not TRUE
SELECT id, a, 
       CASE WHEN NOT a IS TRUE THEN 'TRUE'
            WHEN NOT a IS FALSE THEN 'FALSE'
            ELSE 'NULL'
       END AS not_a_display
FROM logic_test;
 
-- Real-world implication: Negating nullable conditions
-- These are NOT equivalent:
SELECT * FROM users WHERE NOT is_verified;        -- Only FALSE, not NULL
SELECT * FROM users WHERE is_verified = FALSE;    -- Same as above
SELECT * FROM users WHERE is_verified IS NOT TRUE; -- FALSE or NULL
 
-- COALESCE to eliminate NULL ambiguity
SELECT * FROM users
WHERE COALESCE(is_verified, FALSE) = TRUE;  -- Only actually verified
 
SELECT * FROM users
WHERE COALESCE(is_verified, FALSE);          -- Same with implicit TRUE
 

The WHERE Clause and NULL

A WHERE clause only returns rows where the condition evaluates to TRUE. Rows where the condition is FALSE or NULL are excluded. This is why 'WHERE column = NULL' returns nothing—the comparison yields NULL, which is not TRUE.

Boolean Expressions and Predicates

SQL provides several predicates and expressions that return boolean values. Understanding their NULL behavior is critical for correct queries. Common Boolean Predicates:

Boolean Predicates and NULL Behavior
Predicate	Syntax	NULL Input Result	Example
Comparison	a = b, a <> b	NULL	NULL = 5 → NULL
IS NULL	a IS NULL	TRUE/FALSE	NULL IS NULL → TRUE
IS NOT NULL	a IS NOT NULL	TRUE/FALSE	NULL IS NOT NULL → FALSE
BETWEEN	a BETWEEN x AND y	NULL if a is NULL	NULL BETWEEN 1 AND 10 → NULL
IN	a IN (x, y, z)	NULL if a is NULL	NULL IN (1, 2, 3) → NULL
LIKE	a LIKE pattern	NULL if a is NULL	NULL LIKE '%test%' → NULL
EXISTS	EXISTS (subquery)	TRUE/FALSE only	Never returns NULL

boolean_predicates.sql
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
-- Boolean predicate behavior with NULLs
 
-- IS TRUE / IS FALSE / IS UNKNOWN
-- These are the safe way to test three-valued results
SELECT 
    is_verified,
    is_verified IS TRUE AS definitely_true,
    is_verified IS FALSE AS definitely_false,
    is_verified IS UNKNOWN AS its_null
FROM users;
 
-- BETWEEN with NULL
SELECT * FROM products
WHERE price BETWEEN 10 AND 100;
-- If price is NULL, row is NOT included (NULL BETWEEN returns NULL)
 
-- IN with NULL
SELECT * FROM users
WHERE country_code IN ('US', 'CA', 'MX', NULL);
-- NULL values in user.country_code won't match NULL in the list
-- Because NULL = NULL is NULL, not TRUE
 
-- NOT IN: The NULL trap
-- This is dangerous!
SELECT * FROM products
WHERE category_id NOT IN (SELECT category_id FROM discontinued_categories);
-- If subquery returns ANY NULL value, NO rows are returned!
-- Because: value NOT IN (..., NULL, ...) is always NULL
 
-- Safe alternative: Use NOT EXISTS
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM discontinued_categories dc
    WHERE dc.category_id = p.category_id
);
 
-- COALESCE for safe boolean handling
SELECT * FROM users
WHERE COALESCE(is_verified, FALSE);  -- Treat NULL as FALSE
 
-- NULLIF for conditional NULL
SELECT NULLIF(is_active, FALSE) AS active_or_null
FROM users;  -- Returns NULL for inactive, TRUE for active
 
-- Boolean aggregations
SELECT 
    COUNT(*) AS total_users,
    COUNT(is_verified) AS users_with_verification_status,  -- Non-NULL count
    COUNT(*) FILTER (WHERE is_verified) AS verified_users,  -- PostgreSQL
    SUM(CASE WHEN is_verified THEN 1 ELSE 0 END) AS verified_count,  -- Standard
    BOOL_AND(is_active) AS all_active,  -- TRUE if all are TRUE
    BOOL_OR(is_admin) AS any_admin      -- TRUE if any is TRUE
FROM users;

The NOT IN NULL Catastrophe

WHERE x NOT IN (SELECT y FROM table) returns NO rows if the subquery contains any NULL! This is because 'x NOT IN (1, 2, NULL)' evaluates to 'x <> 1 AND x <> 2 AND x <> NULL', and anything AND NULL = NULL (or FALSE). Always use NOT EXISTS for this pattern, or filter NULLs from the subquery.

Vendor-Specific Boolean Implementations

Boolean support varies significantly across database systems. Some have native BOOLEAN types; others emulate boolean with integers or characters.

•Native BOOLEAN type — First-class support since early versions
•Storage: 1 byte
•True literals: TRUE, 't', 'true', 'y', 'yes', 'on', '1'
•False literals: FALSE, 'f', 'false', 'n', 'no', 'off', '0'
•Boolean aggregates: BOOL_AND(), BOOL_OR(), EVERY()
•Canonical form: TRUE/FALSE (not 1/0)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- PostgreSQL boolean examples
CREATE TABLE pg_bool_demo (
    flag BOOLEAN NOT NULL DEFAULT FALSE
);
 
-- Multiple true/false representations accepted
INSERT INTO pg_bool_demo (flag) VALUES 
    (TRUE), ('t'), ('yes'), ('on'), ('1'),
    (FALSE), ('f'), ('no'), ('off'), ('0');
 
-- Aggregations
SELECT BOOL_AND(flag), BOOL_OR(flag), EVERY(flag)
FROM pg_bool_demo;
 
-- Boolean in expressions
SELECT * FROM users WHERE is_active;  -- Clean syntax

Boolean Column Design Patterns

Effective boolean column design requires decisions about nullability, naming conventions, and default values. These patterns help create clear, maintainable schemas.

Boolean Design Guidelines

•Prefer NOT NULL with DEFAULT — Unless 'unknown' is a meaningful state, enforce a value
•Use is_/has_/can_ prefixes — is_active, has_subscription, can_login—makes TRUE/FALSE meaning clear
•Avoid double negatives — is_active (not is_not_inactive)—prevents confusing logic
•Consider nullable semantics — is_verified NULL might mean 'not yet checked' vs FALSE 'checked and failed'
•Default to the safe value — is_admin DEFAULT FALSE, is_locked DEFAULT FALSE
•Index boolean columns selectively — See indexing section below

boolean_patterns.sql
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
-- Pattern 1: Required boolean with safe default
CREATE TABLE accounts (
    account_id      INTEGER PRIMARY KEY,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,   -- Active by default
    is_suspended    BOOLEAN NOT NULL DEFAULT FALSE,  -- Not suspended by default
    is_admin        BOOLEAN NOT NULL DEFAULT FALSE   -- Not admin by default
);
-- No NULL handling needed; all states are explicit
 
-- Pattern 2: Nullable boolean for tri-state
CREATE TABLE applications (
    application_id  INTEGER PRIMARY KEY,
    applicant_name  VARCHAR(100) NOT NULL,
    is_approved     BOOLEAN DEFAULT NULL
    -- NULL = pending review
    -- TRUE = approved
    -- FALSE = rejected
);
 
SELECT * FROM applications WHERE is_approved IS NULL;  -- Pending
SELECT * FROM applications WHERE is_approved = TRUE;   -- Approved
SELECT * FROM applications WHERE is_approved = FALSE;  -- Rejected
 
-- Pattern 3: Timestamp instead of boolean for events
-- Instead of:
-- is_deleted BOOLEAN DEFAULT FALSE
 
-- Prefer:
CREATE TABLE soft_delete_example (
    record_id       INTEGER PRIMARY KEY,
    data            TEXT,
    deleted_at      TIMESTAMP WITH TIME ZONE DEFAULT NULL
);
-- NULL = not deleted, non-NULL = when it was deleted
-- Provides more information and natural querying
 
SELECT * FROM soft_delete_example WHERE deleted_at IS NULL;  -- Active
SELECT * FROM soft_delete_example WHERE deleted_at IS NOT NULL;  -- Deleted
 
-- Pattern 4: Status enum instead of multiple booleans
-- Instead of:
-- is_pending BOOLEAN, is_processing BOOLEAN, is_complete BOOLEAN, is_failed BOOLEAN
 
-- Prefer:
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'complete', 'failed');
 
CREATE TABLE orders (
    order_id        INTEGER PRIMARY KEY,
    status          order_status NOT NULL DEFAULT 'pending'
);
-- Mutually exclusive, clear semantics, extensible
 
-- Pattern 5: Boolean indexing considerations
-- B-tree indexes on boolean columns are rarely useful
-- because there are only 2-3 distinct values
 
-- Instead, use partial indexes for common queries:
CREATE INDEX idx_active_users ON users (user_id) WHERE is_active = TRUE;
CREATE INDEX idx_unverified ON users (email) WHERE is_verified = FALSE;
 
-- These indexes only include matching rows = small and fast

Timestamps Over Booleans for Events

For event-like states (deleted, verified, approved), consider using a timestamp instead of boolean. 'deleted_at TIMESTAMP' captures both whether and when—NULL means active, non-NULL means deleted and records the time. This pattern is standard in Rails (soft delete) and many frameworks.

Boolean Indexing and Performance

Boolean columns present unique indexing challenges due to their extremely low cardinality (only 2-3 possible values). Standard B-tree indexes on boolean columns are often useless or counterproductive. The Problem: With only TRUE/FALSE/NULL values, a B-tree index provides minimal selectivity. If 50% of rows are TRUE and 50% are FALSE, the index offers no advantage over a full table scan—and adds overhead. When Boolean Indexes Help: Very skewed distributions (99% one value, 1% the other) Composite indexes where boolean is one component Partial indexes targeting the rare value When Boolean Indexes Hurt: Roughly even distribution Small tables (sequential scan is faster) Single-column indexes on boolean

boolean_indexing.sql
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
-- Boolean indexing strategies
 
-- Anti-pattern: Standard index on boolean (usually useless)
CREATE INDEX idx_is_active ON users (is_active);  -- Rarely helps
 
-- Better: Partial index for skewed data
-- If 99.9% of users are active, but you query inactive users:
CREATE INDEX idx_inactive_users ON users (user_id) 
WHERE is_active = FALSE;
-- Only indexes ~0.1% of rows; very small and fast
 
-- Partial indexes for common filtered queries
CREATE INDEX idx_verified_emails ON users (email) 
WHERE is_verified = TRUE;
 
CREATE INDEX idx_pending_orders ON orders (created_at) 
WHERE is_processed = FALSE;
 
-- Composite indexes with boolean
-- Boolean as second column can help with specific value
CREATE INDEX idx_active_by_date ON users (created_at, is_active);
-- Useful for: WHERE created_at > '2024-01-01' AND is_active = TRUE
 
-- Order matters in composite indexes
CREATE INDEX idx_status_date ON orders (is_shipped, order_date);
-- Good for: WHERE is_shipped = FALSE ORDER BY order_date
-- Index on (is_shipped) alone still has cardinality issues
 
-- Covering indexes with boolean filter
CREATE INDEX idx_active_user_emails ON users (email) 
INCLUDE (username, created_at)
WHERE is_active = TRUE;
-- PostgreSQL: INCLUDE adds columns to leaf without indexing them
-- Query can be answered from index alone (index-only scan)
 
-- Analyzing index usage
EXPLAIN ANALYZE 
SELECT * FROM users WHERE is_active = TRUE;
 
-- Check for Index Scan vs. Seq Scan
-- If Seq Scan, the index isn't helping
 
-- Statistics on boolean distribution
SELECT 
    is_active,
    COUNT(*) AS count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM users
GROUP BY is_active;
-- If roughly 50/50, standard boolean index won't help

Test Index Effectiveness

Always use EXPLAIN ANALYZE to verify index usage. A boolean index that looks logical might be ignored by the query planner because a sequential scan is faster. Indexes on boolean columns should be partial indexes targeting the less common value.

Boolean in Application Integration

Boolean handling at the database-application boundary requires attention to type mapping, NULL handling, and serialization formats.

Application Integration Considerations

•Language mapping: PostgreSQL BOOLEAN → Java Boolean/boolean, Python bool, JavaScript boolean
•NULL handling: Nullable boolean → Optional<Boolean>, Boolean?, null in most languages
•ORM behavior: Check how your ORM handles boolean columns—some use 0/1, others true/false
•JSON serialization: Boolean columns typically serialize as JSON true/false
•Form inputs: HTML checkboxes only submit when checked—handle missing field as FALSE
•API contracts: Document nullable booleans clearly—null vs. false have different meanings

boolean_integration.sql
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
-- API response patterns
 
-- Pattern 1: Include boolean states explicitly in JSON
SELECT json_build_object(
    'user_id', user_id,
    'username', username,
    'is_active', is_active,
    'is_admin', is_admin,
    'is_verified', COALESCE(is_verified, false)  -- Convert NULL to false for API
) AS user_json
FROM users;
 
-- Pattern 2: Filter based on API requirements
-- Never expose internal admin flags
SELECT json_build_object(
    'user_id', user_id,
    'username', username,
    'is_active', is_active
    -- Omit is_admin from public API
) AS public_user_json
FROM users;
 
-- Pattern 3: Handling checkbox-style updates
-- Checkbox unchecked = field not sent (interpreted as false)
UPDATE users 
SET 
    accepts_marketing = COALESCE(:checkbox_value, FALSE),
    accepts_notifications = COALESCE(:notifications_value, FALSE)
WHERE user_id = :user_id;
 
-- Pattern 4: Audit boolean changes
CREATE TABLE user_audit (
    audit_id        SERIAL PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    field_name      VARCHAR(50) NOT NULL,
    old_value       BOOLEAN,
    new_value       BOOLEAN,
    changed_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    changed_by      INTEGER
);
 
-- Trigger to log boolean changes (PostgreSQL)
CREATE OR REPLACE FUNCTION audit_boolean_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.is_active <> NEW.is_active THEN
        INSERT INTO user_audit (user_id, field_name, old_value, new_value)
        VALUES (NEW.user_id, 'is_active', OLD.is_active, NEW.is_active);
    END IF;
    IF OLD.is_admin <> NEW.is_admin THEN
        INSERT INTO user_audit (user_id, field_name, old_value, new_value)
        VALUES (NEW.user_id, 'is_admin', OLD.is_admin, NEW.is_admin);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_audit_user_booleans
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_boolean_changes();

The Checkbox Problem

HTML checkboxes don't send any value when unchecked—the field is simply missing from the form submission. Your backend must interpret a missing checkbox field as FALSE, not as 'unchanged'. This is a common source of bugs in boolean update handling.

SQLData Types

SQL Data Types

LevelBeginner

Duration60 mins

TopicData Types

4 / 5

Boolean

The Simplest Type With Hidden Complexity

True or false—what could be simpler? Yet the Boolean type in SQL carries surprising complexity. Unlike programming languages where boolean is strictly binary, SQL's Boolean operates in a three-valued logic system where TRUE, FALSE, and NULL (unknown) interact in non-intuitive ways. The Boolean type appears simple but demands understanding of: Three-valued logic (TRUE, FALSE, UNKNOWN) NULL propagation in boolean expressions Vendor-specific implementations and storage Design patterns for nullable vs. non-nullable boolean columns This page provides complete coverage of the SQL Boolean type, ensuring you can use it correctly and avoid the subtle bugs that three-valued logic introduces.

What You Will Learn

By the end of this page, you will master SQL's three-valued Boolean logic, understand how NULL interacts with boolean expressions, navigate vendor-specific implementations, and apply best practices for boolean columns in production schemas.

Boolean Fundamentals

The SQL standard defines a BOOLEAN type with three possible values:

Value	Meaning	Behavior
TRUE	Affirmative	Condition is satisfied
FALSE	Negative	Condition is not satisfied
NULL	Unknown	Condition's truth cannot be determined

This three-valued logic (3VL) distinguishes SQL from most programming languages where boolean is strictly true/false. In SQL, "I don't know" is a valid answer. Why Three Values? Consider a query asking "Is this user verified?" If the is_verified column is NULL, the answer isn't false—it's unknown. Perhaps the verification check hasn't run yet, or the data wasn't collected. NULL represents this uncertainty.

boolean_basics.sql
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
-- Boolean column declarations
CREATE TABLE users (
    user_id         INTEGER PRIMARY KEY,
    username        VARCHAR(50) NOT NULL,
    email           VARCHAR(255) NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,    -- Required, defaults to true
    is_verified     BOOLEAN DEFAULT FALSE,            -- Nullable, defaults to false
    is_admin        BOOLEAN NOT NULL DEFAULT FALSE,   -- Required, defaults to false
    accepts_marketing BOOLEAN                         -- Nullable, no default (NULL)
);
 
-- Boolean literals
INSERT INTO users (user_id, username, email, is_active, is_verified, is_admin)
VALUES 
    (1, 'alice', 'alice@example.com', TRUE, TRUE, FALSE),
    (2, 'bob', 'bob@example.com', TRUE, FALSE, FALSE),
    (3, 'charlie', 'charlie@example.com', FALSE, NULL, FALSE);
 
-- Boolean comparisons
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_verified = FALSE;
SELECT * FROM users WHERE is_admin;              -- Shorthand for = TRUE
SELECT * FROM users WHERE NOT is_admin;          -- Shorthand for = FALSE
 
-- The NULL boolean case
SELECT * FROM users WHERE is_verified IS NULL;    -- User 3
SELECT * FROM users WHERE is_verified IS NOT NULL;
 
-- Common mistake: This returns NO ROWS for NULL values
SELECT * FROM users WHERE is_verified = NULL;     -- WRONG! Always false
-- NULL = NULL returns NULL (unknown), not TRUE
 
-- Correct NULL handling
SELECT * FROM users WHERE is_verified IS TRUE;    -- Only verified users
SELECT * FROM users WHERE is_verified IS FALSE;   -- Only explicitly unverified
SELECT * FROM users WHERE is_verified IS NOT TRUE; -- FALSE or NULL
SELECT * FROM users WHERE is_verified IS NOT FALSE; -- TRUE or NULL

The NULL = NULL Trap

In SQL, NULL = NULL returns NULL (unknown), not TRUE. Use IS NULL and IS NOT NULL for null checks. Similarly, WHERE is_verified = NULL matches no rows. This is one of the most common SQL mistakes.

Three-Valued Logic Deep Dive

SQL's three-valued logic affects all boolean operations. Understanding truth tables for AND, OR, and NOT with NULL is essential for correct query writing. AND Truth Table:

A	B	A AND B
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
TRUE	NULL	NULL
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE
FALSE	NULL	FALSE
NULL	TRUE	NULL
NULL	FALSE	FALSE
NULL	NULL	NULL

Key Insight: FALSE AND anything = FALSE (FALSE dominates). NULL AND TRUE = NULL (uncertainty remains).

OR Truth Table:

A	B	A OR B
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
TRUE	NULL	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE
FALSE	NULL	NULL
NULL	TRUE	TRUE
NULL	FALSE	NULL
NULL	NULL	NULL

Key Insight: TRUE OR anything = TRUE (TRUE dominates). NULL OR FALSE = NULL (uncertainty remains). NOT Truth Table:

A	NOT A
TRUE	FALSE
FALSE	TRUE
NULL	NULL

three_valued_logic.sql
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
65
66
67
68
69
70
71
72
73
74
75
76
-- Demonstrating three-valued logic
 
-- Setup test data
CREATE TABLE logic_test (
    id INT PRIMARY KEY,
    a BOOLEAN,
    b BOOLEAN
);
 
INSERT INTO logic_test VALUES
    (1, TRUE, TRUE),
    (2, TRUE, FALSE),
    (3, TRUE, NULL),
    (4, FALSE, TRUE),
    (5, FALSE, FALSE),
    (6, FALSE, NULL),
    (7, NULL, TRUE),
    (8, NULL, FALSE),
    (9, NULL, NULL);
 
-- AND operations
SELECT id, a, b, 
       a AND b AS and_result,
       CASE 
           WHEN a AND b IS TRUE THEN 'TRUE'
           WHEN a AND b IS FALSE THEN 'FALSE'
           ELSE 'NULL'
       END AS and_display
FROM logic_test;
 
-- Practical implication: Finding active and verified users
-- If is_verified is NULL, this returns no match
SELECT * FROM users
WHERE is_active AND is_verified;  -- NULL is_verified excluded
 
-- OR operations
SELECT id, a, b,
       a OR b AS or_result,
       CASE 
           WHEN a OR b IS TRUE THEN 'TRUE'
           WHEN a OR b IS FALSE THEN 'FALSE'
           ELSE 'NULL'
       END AS or_display
FROM logic_test;
 
-- Practical implication: Show if either condition met
SELECT * FROM users
WHERE is_admin OR is_verified;  -- TRUE if either is TRUE
                                 -- NULL propagates if one is NULL and other is FALSE
 
-- NOT operations
SELECT id, a, NOT a AS not_a_result
FROM logic_test;
 
-- The NOT NULL puzzle
-- NOT NULL returns NULL, not TRUE
SELECT id, a, 
       CASE WHEN NOT a IS TRUE THEN 'TRUE'
            WHEN NOT a IS FALSE THEN 'FALSE'
            ELSE 'NULL'
       END AS not_a_display
FROM logic_test;
 
-- Real-world implication: Negating nullable conditions
-- These are NOT equivalent:
SELECT * FROM users WHERE NOT is_verified;        -- Only FALSE, not NULL
SELECT * FROM users WHERE is_verified = FALSE;    -- Same as above
SELECT * FROM users WHERE is_verified IS NOT TRUE; -- FALSE or NULL
 
-- COALESCE to eliminate NULL ambiguity
SELECT * FROM users
WHERE COALESCE(is_verified, FALSE) = TRUE;  -- Only actually verified
 
SELECT * FROM users
WHERE COALESCE(is_verified, FALSE);          -- Same with implicit TRUE
 

The WHERE Clause and NULL

A WHERE clause only returns rows where the condition evaluates to TRUE. Rows where the condition is FALSE or NULL are excluded. This is why 'WHERE column = NULL' returns nothing—the comparison yields NULL, which is not TRUE.

Boolean Expressions and Predicates

SQL provides several predicates and expressions that return boolean values. Understanding their NULL behavior is critical for correct queries. Common Boolean Predicates:

Boolean Predicates and NULL Behavior
Predicate	Syntax	NULL Input Result	Example
Comparison	a = b, a <> b	NULL	NULL = 5 → NULL
IS NULL	a IS NULL	TRUE/FALSE	NULL IS NULL → TRUE
IS NOT NULL	a IS NOT NULL	TRUE/FALSE	NULL IS NOT NULL → FALSE
BETWEEN	a BETWEEN x AND y	NULL if a is NULL	NULL BETWEEN 1 AND 10 → NULL
IN	a IN (x, y, z)	NULL if a is NULL	NULL IN (1, 2, 3) → NULL
LIKE	a LIKE pattern	NULL if a is NULL	NULL LIKE '%test%' → NULL
EXISTS	EXISTS (subquery)	TRUE/FALSE only	Never returns NULL

boolean_predicates.sql
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
-- Boolean predicate behavior with NULLs
 
-- IS TRUE / IS FALSE / IS UNKNOWN
-- These are the safe way to test three-valued results
SELECT 
    is_verified,
    is_verified IS TRUE AS definitely_true,
    is_verified IS FALSE AS definitely_false,
    is_verified IS UNKNOWN AS its_null
FROM users;
 
-- BETWEEN with NULL
SELECT * FROM products
WHERE price BETWEEN 10 AND 100;
-- If price is NULL, row is NOT included (NULL BETWEEN returns NULL)
 
-- IN with NULL
SELECT * FROM users
WHERE country_code IN ('US', 'CA', 'MX', NULL);
-- NULL values in user.country_code won't match NULL in the list
-- Because NULL = NULL is NULL, not TRUE
 
-- NOT IN: The NULL trap
-- This is dangerous!
SELECT * FROM products
WHERE category_id NOT IN (SELECT category_id FROM discontinued_categories);
-- If subquery returns ANY NULL value, NO rows are returned!
-- Because: value NOT IN (..., NULL, ...) is always NULL
 
-- Safe alternative: Use NOT EXISTS
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM discontinued_categories dc
    WHERE dc.category_id = p.category_id
);
 
-- COALESCE for safe boolean handling
SELECT * FROM users
WHERE COALESCE(is_verified, FALSE);  -- Treat NULL as FALSE
 
-- NULLIF for conditional NULL
SELECT NULLIF(is_active, FALSE) AS active_or_null
FROM users;  -- Returns NULL for inactive, TRUE for active
 
-- Boolean aggregations
SELECT 
    COUNT(*) AS total_users,
    COUNT(is_verified) AS users_with_verification_status,  -- Non-NULL count
    COUNT(*) FILTER (WHERE is_verified) AS verified_users,  -- PostgreSQL
    SUM(CASE WHEN is_verified THEN 1 ELSE 0 END) AS verified_count,  -- Standard
    BOOL_AND(is_active) AS all_active,  -- TRUE if all are TRUE
    BOOL_OR(is_admin) AS any_admin      -- TRUE if any is TRUE
FROM users;

The NOT IN NULL Catastrophe

WHERE x NOT IN (SELECT y FROM table) returns NO rows if the subquery contains any NULL! This is because 'x NOT IN (1, 2, NULL)' evaluates to 'x <> 1 AND x <> 2 AND x <> NULL', and anything AND NULL = NULL (or FALSE). Always use NOT EXISTS for this pattern, or filter NULLs from the subquery.

Vendor-Specific Boolean Implementations

Boolean support varies significantly across database systems. Some have native BOOLEAN types; others emulate boolean with integers or characters.

•Native BOOLEAN type — First-class support since early versions
•Storage: 1 byte
•True literals: TRUE, 't', 'true', 'y', 'yes', 'on', '1'
•False literals: FALSE, 'f', 'false', 'n', 'no', 'off', '0'
•Boolean aggregates: BOOL_AND(), BOOL_OR(), EVERY()
•Canonical form: TRUE/FALSE (not 1/0)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- PostgreSQL boolean examples
CREATE TABLE pg_bool_demo (
    flag BOOLEAN NOT NULL DEFAULT FALSE
);
 
-- Multiple true/false representations accepted
INSERT INTO pg_bool_demo (flag) VALUES 
    (TRUE), ('t'), ('yes'), ('on'), ('1'),
    (FALSE), ('f'), ('no'), ('off'), ('0');
 
-- Aggregations
SELECT BOOL_AND(flag), BOOL_OR(flag), EVERY(flag)
FROM pg_bool_demo;
 
-- Boolean in expressions
SELECT * FROM users WHERE is_active;  -- Clean syntax

Boolean Column Design Patterns

Effective boolean column design requires decisions about nullability, naming conventions, and default values. These patterns help create clear, maintainable schemas.

Boolean Design Guidelines

•Prefer NOT NULL with DEFAULT — Unless 'unknown' is a meaningful state, enforce a value
•Use is_/has_/can_ prefixes — is_active, has_subscription, can_login—makes TRUE/FALSE meaning clear
•Avoid double negatives — is_active (not is_not_inactive)—prevents confusing logic
•Consider nullable semantics — is_verified NULL might mean 'not yet checked' vs FALSE 'checked and failed'
•Default to the safe value — is_admin DEFAULT FALSE, is_locked DEFAULT FALSE
•Index boolean columns selectively — See indexing section below

boolean_patterns.sql
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
-- Pattern 1: Required boolean with safe default
CREATE TABLE accounts (
    account_id      INTEGER PRIMARY KEY,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,   -- Active by default
    is_suspended    BOOLEAN NOT NULL DEFAULT FALSE,  -- Not suspended by default
    is_admin        BOOLEAN NOT NULL DEFAULT FALSE   -- Not admin by default
);
-- No NULL handling needed; all states are explicit
 
-- Pattern 2: Nullable boolean for tri-state
CREATE TABLE applications (
    application_id  INTEGER PRIMARY KEY,
    applicant_name  VARCHAR(100) NOT NULL,
    is_approved     BOOLEAN DEFAULT NULL
    -- NULL = pending review
    -- TRUE = approved
    -- FALSE = rejected
);
 
SELECT * FROM applications WHERE is_approved IS NULL;  -- Pending
SELECT * FROM applications WHERE is_approved = TRUE;   -- Approved
SELECT * FROM applications WHERE is_approved = FALSE;  -- Rejected
 
-- Pattern 3: Timestamp instead of boolean for events
-- Instead of:
-- is_deleted BOOLEAN DEFAULT FALSE
 
-- Prefer:
CREATE TABLE soft_delete_example (
    record_id       INTEGER PRIMARY KEY,
    data            TEXT,
    deleted_at      TIMESTAMP WITH TIME ZONE DEFAULT NULL
);
-- NULL = not deleted, non-NULL = when it was deleted
-- Provides more information and natural querying
 
SELECT * FROM soft_delete_example WHERE deleted_at IS NULL;  -- Active
SELECT * FROM soft_delete_example WHERE deleted_at IS NOT NULL;  -- Deleted
 
-- Pattern 4: Status enum instead of multiple booleans
-- Instead of:
-- is_pending BOOLEAN, is_processing BOOLEAN, is_complete BOOLEAN, is_failed BOOLEAN
 
-- Prefer:
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'complete', 'failed');
 
CREATE TABLE orders (
    order_id        INTEGER PRIMARY KEY,
    status          order_status NOT NULL DEFAULT 'pending'
);
-- Mutually exclusive, clear semantics, extensible
 
-- Pattern 5: Boolean indexing considerations
-- B-tree indexes on boolean columns are rarely useful
-- because there are only 2-3 distinct values
 
-- Instead, use partial indexes for common queries:
CREATE INDEX idx_active_users ON users (user_id) WHERE is_active = TRUE;
CREATE INDEX idx_unverified ON users (email) WHERE is_verified = FALSE;
 
-- These indexes only include matching rows = small and fast

Timestamps Over Booleans for Events

For event-like states (deleted, verified, approved), consider using a timestamp instead of boolean. 'deleted_at TIMESTAMP' captures both whether and when—NULL means active, non-NULL means deleted and records the time. This pattern is standard in Rails (soft delete) and many frameworks.

Boolean Indexing and Performance

Boolean columns present unique indexing challenges due to their extremely low cardinality (only 2-3 possible values). Standard B-tree indexes on boolean columns are often useless or counterproductive. The Problem: With only TRUE/FALSE/NULL values, a B-tree index provides minimal selectivity. If 50% of rows are TRUE and 50% are FALSE, the index offers no advantage over a full table scan—and adds overhead. When Boolean Indexes Help: Very skewed distributions (99% one value, 1% the other) Composite indexes where boolean is one component Partial indexes targeting the rare value When Boolean Indexes Hurt: Roughly even distribution Small tables (sequential scan is faster) Single-column indexes on boolean

boolean_indexing.sql
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
-- Boolean indexing strategies
 
-- Anti-pattern: Standard index on boolean (usually useless)
CREATE INDEX idx_is_active ON users (is_active);  -- Rarely helps
 
-- Better: Partial index for skewed data
-- If 99.9% of users are active, but you query inactive users:
CREATE INDEX idx_inactive_users ON users (user_id) 
WHERE is_active = FALSE;
-- Only indexes ~0.1% of rows; very small and fast
 
-- Partial indexes for common filtered queries
CREATE INDEX idx_verified_emails ON users (email) 
WHERE is_verified = TRUE;
 
CREATE INDEX idx_pending_orders ON orders (created_at) 
WHERE is_processed = FALSE;
 
-- Composite indexes with boolean
-- Boolean as second column can help with specific value
CREATE INDEX idx_active_by_date ON users (created_at, is_active);
-- Useful for: WHERE created_at > '2024-01-01' AND is_active = TRUE
 
-- Order matters in composite indexes
CREATE INDEX idx_status_date ON orders (is_shipped, order_date);
-- Good for: WHERE is_shipped = FALSE ORDER BY order_date
-- Index on (is_shipped) alone still has cardinality issues
 
-- Covering indexes with boolean filter
CREATE INDEX idx_active_user_emails ON users (email) 
INCLUDE (username, created_at)
WHERE is_active = TRUE;
-- PostgreSQL: INCLUDE adds columns to leaf without indexing them
-- Query can be answered from index alone (index-only scan)
 
-- Analyzing index usage
EXPLAIN ANALYZE 
SELECT * FROM users WHERE is_active = TRUE;
 
-- Check for Index Scan vs. Seq Scan
-- If Seq Scan, the index isn't helping
 
-- Statistics on boolean distribution
SELECT 
    is_active,
    COUNT(*) AS count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM users
GROUP BY is_active;
-- If roughly 50/50, standard boolean index won't help

Test Index Effectiveness

Always use EXPLAIN ANALYZE to verify index usage. A boolean index that looks logical might be ignored by the query planner because a sequential scan is faster. Indexes on boolean columns should be partial indexes targeting the less common value.

Boolean in Application Integration

Boolean handling at the database-application boundary requires attention to type mapping, NULL handling, and serialization formats.

Application Integration Considerations

•Language mapping: PostgreSQL BOOLEAN → Java Boolean/boolean, Python bool, JavaScript boolean
•NULL handling: Nullable boolean → Optional<Boolean>, Boolean?, null in most languages
•ORM behavior: Check how your ORM handles boolean columns—some use 0/1, others true/false
•JSON serialization: Boolean columns typically serialize as JSON true/false
•Form inputs: HTML checkboxes only submit when checked—handle missing field as FALSE
•API contracts: Document nullable booleans clearly—null vs. false have different meanings

boolean_integration.sql
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
-- API response patterns
 
-- Pattern 1: Include boolean states explicitly in JSON
SELECT json_build_object(
    'user_id', user_id,
    'username', username,
    'is_active', is_active,
    'is_admin', is_admin,
    'is_verified', COALESCE(is_verified, false)  -- Convert NULL to false for API
) AS user_json
FROM users;
 
-- Pattern 2: Filter based on API requirements
-- Never expose internal admin flags
SELECT json_build_object(
    'user_id', user_id,
    'username', username,
    'is_active', is_active
    -- Omit is_admin from public API
) AS public_user_json
FROM users;
 
-- Pattern 3: Handling checkbox-style updates
-- Checkbox unchecked = field not sent (interpreted as false)
UPDATE users 
SET 
    accepts_marketing = COALESCE(:checkbox_value, FALSE),
    accepts_notifications = COALESCE(:notifications_value, FALSE)
WHERE user_id = :user_id;
 
-- Pattern 4: Audit boolean changes
CREATE TABLE user_audit (
    audit_id        SERIAL PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    field_name      VARCHAR(50) NOT NULL,
    old_value       BOOLEAN,
    new_value       BOOLEAN,
    changed_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    changed_by      INTEGER
);
 
-- Trigger to log boolean changes (PostgreSQL)
CREATE OR REPLACE FUNCTION audit_boolean_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.is_active <> NEW.is_active THEN
        INSERT INTO user_audit (user_id, field_name, old_value, new_value)
        VALUES (NEW.user_id, 'is_active', OLD.is_active, NEW.is_active);
    END IF;
    IF OLD.is_admin <> NEW.is_admin THEN
        INSERT INTO user_audit (user_id, field_name, old_value, new_value)
        VALUES (NEW.user_id, 'is_admin', OLD.is_admin, NEW.is_admin);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_audit_user_booleans
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_boolean_changes();

The Checkbox Problem

HTML checkboxes don't send any value when unchecked—the field is simply missing from the form submission. Your backend must interpret a missing checkbox field as FALSE, not as 'unchanged'. This is a common source of bugs in boolean update handling.

Boolean

The Simplest Type With Hidden Complexity

Boolean Fundamentals

Three-Valued Logic Deep Dive

Boolean Expressions and Predicates

Vendor-Specific Boolean Implementations

Boolean Column Design Patterns

Boolean Indexing and Performance

Boolean in Application Integration

There’s more to understand.

Boolean

The Simplest Type With Hidden Complexity

Boolean Fundamentals

Three-Valued Logic Deep Dive

Boolean Expressions and Predicates

Vendor-Specific Boolean Implementations

Boolean Column Design Patterns

Boolean Indexing and Performance

Boolean in Application Integration

There’s more to understand.