Home Database Basics 2. NULL Values

2. NULL Values

SQL has a special value, NULL, used to represent missing or undefined data. NULL is different and distinct from all other types of data. NULL is not the same as zero, it's not the same as false, and it's not the same as an empty string (except in Oracle used by ROI CRM). In SQL, NULL can never equal anything else and the result of any expression that compares NULL to some other value or combines NULL with another value is always NULL. In fact, NULL does not even equal NULL. The result of all of the expressions below is NULL:

  • NULL = ''
  • 47 = NULL
  • NULL = NULL
  • NULL = 0
  • NULL + 800
  • 24 - NULL
  • NULL < 90

This might seem confusing right now. This is good! When you think about NULL, I want it to set off a little tingly sensation in your brain that says "hmm something may be different or strange here!" Comparisons to NULL and misunderstandings about how NULL operates are root causes of many mistakes in SQL. Being aware that NULL exists and using extra caution when data or operations may contain or result in NULL values is crucial to writing good SQL. Leveraging NULL to your benefit can also help you write more efficient, performant code.

Three-Valued Logic

SQL uses what's called Three-Valued Logic when evaluating logical expressions. In day-to-day life, you are probably used to logical expressions being either true or false, for example:

  • It rained today: false
  • I am 42 years old: true
  • The Portland Thorns are the best team in the NWSL: true (objective fact forever and always)

However, in SQL, every logical expression can have three possible values

  1. true
  2. false
  3. NULL

So any time you're doing something in SQL and thinking "the answer to this question is either going to be true or false", remember, it could also be NULL.

Rules to live by

We are going to talk more about the practical implications of NULL values and how to deal with them in relevant chapters to come. For now, I'd like you to memorize these two crucial rules:

  1. Any time NULL is compared to or combined with another value, the result is always NULL.
  2. NULL is not the same as false.