Relational Model

Null Values and Three Valued Logic

Null means the absence of a value, not zero and not empty. Comparisons with null produce unknown, which turns two valued logic into three and changes how conditions, keys and aggregates behave.

On this page (13)

Concept

Null is a marker meaning no value is present. It is not zero, not an empty string and not a blank. It is the absence of a value, and it carries at least three distinct real world meanings that the model cannot tell apart.

Meaning of null	Example
Unknown — a value exists but is not recorded	A student has a date of birth; the office has not entered it
Not applicable — no value can exist	The maiden name of an unmarried person
Not yet — a value will exist later	The discharge date of a currently admitted patient

The database stores one null for all three. If the difference matters to the business, model it explicitly — for example a status column — rather than expecting null to carry the distinction.

Three valued logic

Any comparison involving null yields unknown, not true and not false. Logic therefore has three values.

  AND      | true    | false | unknown
  ---------+---------+-------+---------
  true     | true    | false | unknown
  false    | false   | false | false
  unknown  | unknown | false | unknown

  OR       | true    | false   | unknown
  ---------+---------+---------+---------
  true     | true    | true    | true
  false    | true    | false   | unknown
  unknown  | true    | unknown | unknown

  NOT      |
  ---------+---------
  true     | false
  false    | true
  unknown  | unknown      <- NOT unknown is still unknown

Two entries carry most of the surprises. false AND unknown is false, because a false operand settles an AND regardless. true OR unknown is true, for the mirror reason. Everything else involving unknown stays unknown.

The rule that explains every surprise

  A row is returned only when the condition evaluates to TRUE.
  UNKNOWN is not TRUE, so the row is NOT returned.

  This single sentence explains all of the following.

Null is not equal to null

  null = null      -> unknown, NOT true
  null <> null     -> unknown
  null = 5         -> unknown
  null + 5         -> null    (arithmetic propagates null)

  Two unknown values cannot be shown to be equal, because
  neither is known. That is logically correct and it is why
  a special test is needed:

  value IS NULL        -> true or false, never unknown
  value IS NOT NULL    -> true or false, never unknown

A condition and its negation can both exclude a row

  marks
    87
    null
    45

  condition  marks > 50   returns 87 only
  condition  marks <= 50  returns 45 only

  The null row appears in NEITHER result, because both
  conditions evaluate to unknown for it. Together the two
  queries do not cover the table, which is exactly the
  trap this topic is famous for.

Aggregates ignore nulls, except one

  marks: 87, null, 45

  COUNT(*)       -> 3     counts ROWS, nulls included
  COUNT(marks)   -> 2     counts NON NULL values
  SUM(marks)     -> 132   nulls skipped
  AVG(marks)     -> 66    132 / 2, NOT 132 / 3

  The average divides by the number of non null values.
  If null should have counted as zero, the query must say
  so explicitly - the database will not assume it.

Nulls and keys

Where	Null allowed?	Why
Primary key	Never	Entity integrity — an unknown value cannot identify
Part of a composite primary key	Never	Same rule applies to every part
Foreign key	Yes, unless declared not null	Expresses an optional relationship
Unique constraint	Usually yes, often more than once	Two nulls are not equal, so they do not conflict. Products vary here.

Designing to avoid nulls

Declare not null wherever the value is genuinely required. Total participation in the ER model translates directly into this.
Use a default where a sensible one exists — status pending, quantity zero.
Split the table when a group of columns is null for most rows. That is usually a subclass in disguise, and Phase 8 will say the same thing in the language of normalisation.
Do not use a sentinel value. Storing 0 or 9999 or an empty string to mean unknown replaces one problem with a worse one, because those values participate in arithmetic and comparisons as if they were real.

Example

-- Illustration only.
-- students ( roll_no, name, marks, dept_code )
--   21 Meera  87   CS
--   22 Ravi   null CS      -- absent for the test
--   23 Anitha 45   null    -- not yet assigned a department

-- Returns Meera only. Ravi is excluded by unknown.
-- SELECT name FROM students WHERE marks > 50;

-- Returns Ravi. IS NULL is the only reliable test.
-- SELECT name FROM students WHERE marks IS NULL;

-- Treats an absent mark as zero, deliberately and visibly.
-- SELECT name, COALESCE(marks, 0) AS marks FROM students;

Common mistakes

Writing a comparison against null. It is never true. Use the null test.
Expecting a condition and its negation to cover every row. Nulls fall outside both.
Assuming an average treats null as zero. It divides by the non null count.
Using an empty string or zero as a sentinel for unknown. Those are real values and behave like real values.
Allowing null where the requirement said total participation. That is a lost constraint.
Assuming unique columns reject a second null. Behaviour differs between products; check before relying on it.

Exam and interview questions

What does null represent, and what are its three real world meanings?
Write the truth tables for AND, OR and NOT in three valued logic.
Why is null equal to null not true?
Given a column with a null, explain why neither a condition nor its negation returns that row.
How do count, sum and average treat nulls?
Where are nulls permitted among primary keys, foreign keys and unique constraints?

Practice

Evaluate: true AND unknown, false OR unknown, NOT unknown, unknown AND unknown.
Given marks 60, null, 40, compute count of rows, count of marks, sum and average.
Take the hospital schema from Phase 4b and justify every nullable column, or remove it.

Conclusion

Null is absence, comparisons with it produce unknown, and only a true condition returns a row. Test with the null test rather than equality, remember that aggregates skip nulls, and design them out wherever the requirement genuinely demands a value.

Useful resources

Hand picked references for this topic

PostgreSQL Transaction Isolation

Official explanation of isolation levels and the anomalies each one permits.

Topics #DBMS #Integrity Constraints #Intermediate #Interview #Placement #Relational Model

Written by Lorens Mishra

Default administrator account created by the installer.