NULL

SQL has a special value called NULL. It represents nothing, N/A, unknown, invalid, etc. Let's look at an example.

You're building a restaurant review app called "Yulp", and the restaurants you support are in a table called restaurants. Have a look at the preview and notice the NULL values.

Several of these columns have NULL in them. NULL can take the place of a VARCHAR, NUMERIC, or any data type. What exactly a NULL means depends on what the engineers were thinking when they put it there.

For this restaurants table, the cuisine_type can be NULL. This might mean "unknown" because we don't know what kind of cuisine it is. It could mean "N/A" because the restaurant is so avant-garde that it cannot be categorized.

Why could the address be NULL? Maybe the restaurant is delivery-only, with no dine-in or pick-up option, and therefore need not present an address. The delivery fee being NULL might mean the restaurant doesn't do delivery, or maybe that delivery is free. Arguably, if delivery is free, the delivery fee should just be $0.00. However, you will often find that data in the real world isn't always intuitive.

Checking for NULL

Let's query all restaurants where the cuisine_type is NULL. Easy query, right?

SELECT *
FROM restaurants
WHERE cuisine_type = NULL;

We get no rows! What's going on?

A counter-intuitive feature of NULL is that it cannot be compared with any other values, not even itself. This is because NULL is supposed to represent a value which cannot be determined, so you wouldn't expect be able to determine whether it equals (or does not equal) some other value. Imagine if you didn't know how old Alice and Bob are, and then someone asks you if they are the same age (if their ages are equal). If you represented an unknown age with NULL, and compared Alice's and Bob's ages (compare NULL with NULL), the result should also be NULL. If the result were either TRUE (they are the same age) or FALSE (they are not the same age), it would mean that a certain answer was made, which is impossible in this scenario.

Have a look at these comparisons.

SELECT
  NULL > 2 AS "greater_than_2?",
  NULL <= 2 AS "less_than_equal_to_2?",
  NULL = NULL AS "equal_itself?",
  NULL != 'Andy' AS "not_equal_string?";

We might expect these comparisons to evaluate to either TRUE or FALSE. However, they are all just NULL! The reason we get 0 rows when using WHERE cuisine_type = NULL is that rows having a NULL result in the WHERE clause get filtered out, just the same as rows having a FALSE result.

So how can we get rows with NULL values? SQL has a special operator for this.

SELECT *
FROM restaurants
WHERE cuisine_type IS NULL;

Calculations with NULL

NULL also produces NULL when passed into most functions or math operations.

SELECT
  2 + NULL AS null_addition,
  2 * NULL AS null_multiplication,
  power(2, NULL) AS null_exponentiation,
  upper(NULL) AS string_manipulation;

Double-Quoted Identifiers

Did you notice what I did here?

SELECT NULL = NULL AS "equal_itself?";

The alias has quotation marks (double-quotes as opposed to single-quotes). This is because a question mark is part of the alias name. Without it, we get an error!

SELECT NULL = NULL AS equal_itself?;

error: syntax error at or near "?"

This is because SQL's rules limit identifiers (the names for tables, columns, aliases, functions, etc.) to specific characters, e.g. they cannot normally contain spaces or certain punctuation marks. However, if you insist on having those special characters, you can wrap that identifier in double-quotes.

SELECT 2 + 2 AS "basic arithmetic problem";

Remember that single-quotes are for string literals and double-quotes are for identifiers with special characters!