Boolean logic in SQL: AND, OR, NOT and the NULL three-valued trap

Q: Is a OR NOT a always TRUE in SQL?

Only when a is TRUE or FALSE. When a is NULL the expression is NULL, so a WHERE with that condition drops the row. The pitfall page shows the two-valued check beside the three-valued behaviour.

SQL has AND, OR and NOT with the usual precedence, and a third truth value, NULL, that silently changes what a rule does to rows with missing data. The listing below is the engine's PostgreSQL export of the entry rule, with the guard it generates against exactly that.

Logic	SQL	Note
`NOT A`	`NOT a`	`NOT NULL` is NULL
`A AND B`	`a AND b`	FALSE wins over NULL
`A OR B`	`a OR b`	TRUE wins over NULL
`A XOR B`	`a <> b` on Booleans	NULL if either is NULL
`A -> B`	`NOT a OR b`	No implication operator
`A <-> B`	`a = b` on Booleans	NULL if either is NULL
`TRUE`, `FALSE`	`TRUE`, `FALSE`	And `NULL`, the third value

On TRUE and FALSE, SQL's operators are the Boolean ones: NOT first, then AND, then OR. The difference is NULL, which means unknown. NULL AND FALSE is FALSE and NULL OR TRUE is TRUE, because the known operand decides; NULL AND TRUE, NULL OR FALSE and NOT NULL are all NULL. A WHERE clause keeps only rows whose condition is TRUE, so a NULL condition drops the row, and NOT (x = 1) does not return the rows where x is NULL.

The export does not pretend NULL away. Its header states a non-null input precondition: every fragment is defined only when the input columns it reads are non-NULL Booleans, and the guard template marks a row with a NULL input as invalid_input, a row that violates the declared assumptions as out_of_scope, and fills the output columns only for ok rows. It explicitly warns against wrapping the fragments in COALESCE(…, FALSE), which would choose a meaning for unknown inputs that the rule never stated.

The file is read-only by construction: only WITH, VALUES and SELECT, no DDL or DML, no procedures. The header names the target, PostgreSQL 16 or later with typed Boolean inputs, and the version it was tested with, and says it is not a claim of compatibility with other dialects; IS TRUE, IS NOT TRUE and Boolean columns are where dialects differ most. The pitfall page on NULL walks through a OR NOT a, a tautology on two values that is NULL when a is.

The entry rule, as the engine exports it

allow_entry := (member OR invited) AND NOT banned

-- Tangibool PostgreSQL export (tangibool.postgresql/1)
-- Target: PostgreSQL 16 or later with typed boolean inputs; tested with PostgreSQL 18.3 (PGlite 0.5.8).
-- Not a claim of compatibility with other SQL dialects.
-- Model: not stated
-- Source revision: reference
-- Interpretation status: not stated
-- Exporter: 1.0.0 (engine 1.0.0; semantics tangibool-semantics/1)
-- Logic digest: f0daa82174670c788f59eb058d07f7601cd0ace4ed94d61a747cc26c41fd4e0d
-- Assumptions digest: d58ee1ad38e23e5093e4baa1047b5ae70d6972e08f555e34ac3fa16df63c4a78
-- Mode: decision
--
-- Read-only: this file contains only WITH / VALUES / SELECT statements. No DDL, DML,
-- procedures, credentials, network access, or database changes. The expressions are pure
-- Boolean expressions, so their result does not depend on SQL evaluation order.
--
-- NON-NULL INPUT PRECONDITION: NULL is not a truth value of this rule. Every fragment below
-- is defined only when every input column it reads is a non-NULL boolean. The guard template
-- (section 2) marks any NULL input as 'invalid_input' and any row that violates the
-- assumptions as 'out_of_scope', and fills output columns only for 'ok' rows; a NULL in an
-- output column of another row is an envelope marker, not a third truth value. Never wrap
-- the fragments in COALESCE(..., FALSE) or otherwise choose a meaning for unknown inputs.
-- This evaluates the supplied model; it does not show that the model is a complete, legal,
-- or safe statement of a real-world rule.
--
-- Input columns (stable ID: name -> column):
--   v_79df34978043: member -> "in_member"
--   v_8afde06f51e3: invited -> "in_invited"
--   v_60b2536f5dc8: banned -> "in_banned"
-- Output columns:
--   f_ae8686d89791: allow_entry -> "out_allow_entry"
-- Assumptions (scope K; exported separately and never folded into an output):
--   none; every complete input row is in scope.

-- ----------------------------------------------------------------------------------------------
-- 1. Expression fragments (parenthesised; non-NULL boolean inputs required)
-- ----------------------------------------------------------------------------------------------
--
-- [out_allow_entry] output allow_entry (stable ID f_ae8686d89791)
--   allow_entry := (member OR invited) AND NOT banned
--   PRECONDITION: "in_member", "in_invited", "in_banned" are non-NULL booleans.
--   (("in_member" OR "in_invited") AND (NOT "in_banned"))
--

-- ----------------------------------------------------------------------------------------------
-- 2. Guard template (read-only SELECT over an inputs list)
-- ----------------------------------------------------------------------------------------------
-- Replace the example rows between the markers with your own rows, or replace the inputs
-- CTE with a SELECT that provides the boolean columns listed in the header. status is
-- 'invalid_input' when any input is NULL, 'out_of_scope' when the assumptions are false, and
-- 'ok' otherwise; output columns are filled only for 'ok' rows (NULL elsewhere is an
-- envelope marker). Assumption columns are shown for rows with complete inputs.
WITH inputs ("row_id", "in_member", "in_invited", "in_banned") AS (
  -- tangibool:example-rows:begin
  VALUES
    (1, FALSE, FALSE, FALSE),
    (2, FALSE, FALSE, TRUE),
    (3, FALSE, TRUE, FALSE),
    (4, FALSE, TRUE, TRUE),
    (5, TRUE, FALSE, FALSE),
    (6, TRUE, FALSE, TRUE),
    (7, TRUE, TRUE, FALSE),
    (8, TRUE, TRUE, TRUE),
    (9, NULL::boolean, FALSE, FALSE)
  -- tangibool:example-rows:end
),
checked AS (
  SELECT
    inputs.*,
    CASE
      WHEN "in_member" IS NULL OR "in_invited" IS NULL OR "in_banned" IS NULL THEN 'invalid_input'
      ELSE 'ok'
    END AS "status"
  FROM inputs
)
SELECT
  "row_id",
  "in_member",
  "in_invited",
  "in_banned",
  "status",
  CASE WHEN "status" = 'ok' THEN (("in_member" OR "in_invited") AND (NOT "in_banned")) END AS "out_allow_entry"
FROM checked
ORDER BY "row_id";

The common slip

Testing a rule with WHERE NOT banned and forgetting the rows where banned is NULL: they are neither banned nor not banned, so they vanish from both the allowed and the rejected set. The export's guard reports them as invalid_input instead.

Questions

Is a OR NOT a always TRUE in SQL?

Only when a is TRUE or FALSE. When a is NULL the expression is NULL, so a WHERE with that condition drops the row. The pitfall page shows the two-valued check beside the three-valued behaviour.

Why does the export target PostgreSQL only?

Because that is what it was tested against, and the header says so; the Boolean type, IS TRUE and the WITH syntax differ elsewhere. The pure Boolean fragments usually port, the guard template may not.

Guides

SQL IS NULL and IS NOT NULL

Languages

Generated from the Tangibool engine, semantics tangibool-semantics/1, engine 1.0.0, build 2026-11-07. Every table, map, simplification and verdict on these pages was computed by the Tangibool engine when the site was built, and every claim was re-checked by the same exact checker the app uses. Limits are stated where they apply. © 2026 Foreranger LTD.