Skip to content
Coordinates of ThoughtHome of Philosophy
Join
Question · Explain Newcomer-friendly

Why does my SQL query treat NULL = NULL as neither true nor false?

Nur Aisyah Rahman Fellow
· 4 replies
Modal & non-classical logicAnalyticStephen Kleene#databases#sql-null#three-valued-logic
Follow

Beginner question from a data analyst. Last week a report of mine undercounted customers by about four per cent. The bug was a filter like WHERE region <> 'KL', which I assumed would return everyone outside Kuala Lumpur. It also silently dropped every customer whose region was missing.

A colleague shrugged and said, "SQL uses three-valued logic." Apparently NULL = NULL doesn't come out true. It comes out as a third thing.

So, three questions:

  1. Is this the same "many-valued logic" philosophers talk about, or just a database quirk with a fancy name?
  2. If it is a real logic, which one?
  3. What is NULL supposed to mean? "We don't know the region" and "this customer has no region" seem like very different situations, but the database treats them the same.
LikeAnswer
Follow4 answers

Join the conversation: members reply here, and joining is free. Reading is always free.

Join free to reply Sign in

4 answers

Accepted answer

  1. Derek Hollis

    ✓ Accepted answer Fellow

    It's a real logic, and a well-known one. SQL's AND, OR and NOT follow Kleene's strong three-valued tables, with the third value called UNKNOWN:

    • TRUE OR UNKNOWN = TRUE (one true disjunct is enough)
    • FALSE AND UNKNOWN = FALSE (one false conjunct is enough)
    • anything else involving UNKNOWN stays UNKNOWN, and NOT UNKNOWN is UNKNOWN

    Any comparison with NULL, including NULL = NULL, yields UNKNOWN. Then the crucial bit: a WHERE clause keeps only rows where the condition is TRUE. UNKNOWN rows are dropped exactly as FALSE rows are. That's your four per cent.

    A nice consequence for the philosophically minded: WHERE region = 'KL' OR region <> 'KL' does not return every row. Excluded middle fails in your own query. The fix is IS NULL, IS NOT NULL or IS DISTINCT FROM, which are two-valued.

    On your third question you've put your finger on a real mess. NULL is used both for "applicable but unknown" and "not applicable", which a logician would model differently. E. F. Codd, who designed the relational model, later proposed splitting it into two kinds of missing value for exactly this reason. The industry mostly didn't follow him.

    Helpful · 2

Other answers

  1. Nur Aisyah Rahman

    Fellow

    "Excluded middle fails in your own query" is going on a sticky note above my monitor. Thank you, Derek. So the logic is Kleene's, and the confusion about what NULL means is a separate problem nobody fully solved.

    Helpful · 3
  2. Tomasz Szewczak

    Fellow

    A small twist to make it worse. A CHECK constraint in SQL rejects a row only if the condition comes out FALSE, so UNKNOWN passes. In a WHERE clause UNKNOWN is treated like false; in a CHECK constraint it is treated like true. The database isn't running one consistent three-valued logic, it's running Kleene's connectives and then making two different decisions about what to do with the middle value. That's not a criticism of Kleene. It's a reminder that choosing the logic and choosing how to act on its values are separate choices.

  3. Wanjiru Njenga

    Fellow

    From credit scoring: in our data, a missing value is often the most informative thing in the row. People leave a field blank for reasons. So "unknown" is not neutral, and any logic that treats it as neutral can mislead you. I usually code missingness explicitly as its own category before any filter touches it.

    Helpful · 1