SQL BOOLEAN Data Type: TRUE, FALSE, and NULL Handling

Your product catalogue has an is_active column that controls whether a product appears on the storefront. A query that accidentally pulls inactive products into a revenue report, or misses them entirely because of a NULL in the column, leads to wrong numbers. Boolean columns look simple but they carry specific rules around NULL that catch a lot of analysts off guard.

How SQL Defines Boolean Values

A boolean represents one of two states: true or false. SQL adds a third state: unknown, represented by NULL. This three-value logic affects every condition you write.

| Value | Meaning | Stored in SQLite as | |-------|---------|-------------------| | TRUE | Condition is true | 1 | | FALSE | Condition is false | 0 | | NULL | Value is unknown or missing | NULL |

In SQLite, the keywords TRUE and FALSE are valid and resolve to 1 and 0 respectively. You will also see boolean-style columns stored as TINYINT(1) in MySQL schemas.

-- is_active uses 1 for true and 0 for false in SQLite
SELECT p.product_id, p.product_name, p.is_active
FROM products p
ORDER BY p.is_active DESC, p.product_name
LIMIT 10;

Filtering Boolean Columns

The most common use of a boolean column is filtering. There are two equivalent ways to do this in SQLite.

-- Both of these return the same result
SELECT p.product_name, p.price
FROM products p
WHERE p.is_active = 1;

SELECT p.product_name, p.price
FROM products p
WHERE p.is_active = TRUE;

-- Inactive products
SELECT p.product_name, p.price, p.stock_qty
FROM products p
WHERE p.is_active = 0
ORDER BY p.product_name;

Most teams prefer WHERE p.is_active = 1 in SQLite because it is explicit and maps directly to what is stored. In PostgreSQL you would write WHERE is_active = TRUE or simply WHERE is_active.

NULL: The Third State

NULL is not TRUE and it is not FALSE. It means the value is absent or unknown. This matters because any comparison with NULL returns NULL, not TRUE or FALSE.

-- This returns NULL, not TRUE or FALSE
SELECT NULL = 1;     -- result: NULL
SELECT NULL = 0;     -- result: NULL
SELECT NULL = NULL;  -- result: NULL

-- Find products where is_active is NULL
SELECT p.product_id, p.product_name, p.is_active
FROM products p
WHERE p.is_active IS NULL;

-- Return all non-active products, including NULLs
SELECT p.product_id, p.product_name, p.is_active
FROM products p
WHERE p.is_active = 0 OR p.is_active IS NULL;

IS NULL and IS NOT NULL

The only correct way to check for NULL is with IS NULL or IS NOT NULL. Using = NULL never works.

-- Find orders with no notes recorded
SELECT o.order_id, o.order_date, o.status
FROM orders o
WHERE o.notes IS NULL
ORDER BY o.order_date DESC;

-- Find orders that do have notes
SELECT o.order_id, o.order_date, o.notes
FROM orders o
WHERE o.notes IS NOT NULL
  AND o.notes != ''
ORDER BY o.order_date DESC;

-- Find payments with no failure reason, meaning they likely succeeded
SELECT pay.payment_id, pay.order_id, pay.amount, pay.method, pay.status
FROM payments pay
WHERE pay.failure_reason IS NULL
ORDER BY pay.payment_date DESC
LIMIT 15;

Using COALESCE to Handle NULL in Boolean Columns

COALESCE() returns the first non-null value from a list. It is useful when you want to treat a NULL boolean column as a default value.

-- Treat NULL is_active as 0 (inactive)
SELECT
    p.product_name,
    p.price,
    COALESCE(p.is_active, 0) AS is_active_clean
FROM products p
ORDER BY is_active_clean DESC, p.product_name;

-- Use COALESCE to label boolean values as readable text
SELECT
    p.product_name,
    CASE COALESCE(p.is_active, 0)
        WHEN 1 THEN 'Active'
        ELSE 'Inactive'
    END AS status_label
FROM products p
ORDER BY p.product_name;

Common Mistakes

-- wrong
SELECT p.product_name
FROM products p
WHERE p.is_active = NULL;

-- correct
SELECT p.product_name
FROM products p
WHERE p.is_active IS NULL;

= NULL always evaluates to NULL, never to TRUE, so this query returns zero rows regardless of what is in the table.

---

-- wrong
SELECT p.product_name
FROM products p
WHERE p.is_active != 1;

-- correct
SELECT p.product_name
FROM products p
WHERE p.is_active != 1 OR p.is_active IS NULL;

!= 1 excludes rows where is_active is NULL because NULL != 1 evaluates to NULL, not TRUE. If you want all non-active products including those with a missing value, add the IS NULL check.

---

-- wrong
SELECT COUNT(*) AS inactive_count
FROM products p
WHERE p.is_active = FALSE;

-- correct
SELECT COUNT(*) AS inactive_count
FROM products p
WHERE p.is_active = 0 OR p.is_active IS NULL;

WHERE is_active = FALSE only catches rows stored as 0. Rows where is_active is NULL are silently missed, undercounting your inactive products.

Quick Recap

• SQL boolean logic has three states: TRUE, FALSE, and NULL. SQLite stores them as 1, 0, and NULL respectively.
• Any comparison with NULL using = or != returns NULL, not TRUE or FALSE. Always use IS NULL or IS NOT NULL to check for missing values.
• When a boolean column allows nulls, WHERE col = 0 alone will not catch NULL rows. Use OR col IS NULL or COALESCE() to handle all non-true cases explicitly.

SELECT p.product_name, p.price, p.stock_qty FROM products p WHERE p.is_active = 1 ORDER BY p.product_name ASC;

SELECT o.order_id, o.order_date, o.status, o.notes FROM orders o WHERE o.status = 'Delivered' AND o.notes IS NOT NULL AND o.notes != '' ORDER BY o.order_date DESC;

SELECT cat.category_name, COUNT(CASE WHEN p.is_active = 1 THEN 1 END) AS active_products, COUNT(CASE WHEN p.is_active = 0 OR p.is_active IS NULL THEN 1 END) AS inactive_products FROM categories cat JOIN products p ON cat.category_id = p.category_id GROUP BY cat.category_id, cat.category_name ORDER BY cat.category_name ASC;