NULL Values in SQL Queries

Today’s post is about NULL values in SQL, and comes courtesy of my friend and database wizard, Kaley. You should check out his website if you’d like to learn more about SQL, Oracle database, and making queries run faster .

---

Here’s a topic that gets a lot of budding developers in trouble–the concept of NULL values in SQL queries.

Whenever you issue a SQL query to a database…and you want to know whether a column has a NULL value in it…what is the proper way to write a query that will find the result?

Should you use a query like this?

SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN = NULL

Or! Should you use a query like this?

SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN IS NULL

…The answer is, you should be using the second query (WHERE SOME_COLUMN IS NULL).

Now why is that?

We don’t use the “IS” keyword with any other comparisons in the database, right?

If we want to know if a field is equal to one, we use a WHERE clause like this:

WHERE SOME_COLUMN = 1

So why on earth would we do the IS keyword with a NULL value? Why would we need to treat NULL differently?

The answer is this: In SQL, NULL represents the concept of “unknown” (so a NULL value represents an “unknown” value).

Null as an Unknown

In most databases, there is a difference between NULL and an empty string (represented by a “double apostrophe” or ”).

But this isn’t always true for all databases:  For example, Oracle database won’t allow you to have an empty string. Anytime Oracle database sees an empty string, it automatically converts the empty string into a NULL value.

For the majority of the other databases out there, though, a NULL value is treated differently than an empty string:

• An empty string is treated like a known value where there is no value.
• A NULL value is treated like a value that is not known.

This would be the difference between me asking the question, “What was U.S President Theodore Roosevelt’s middle name?”

• One answer might be, “Well, I don’t know what Theodore Roosevelt’s middle name is.” (This idea could be represented by a NULL value in the MIDDLE_NAME column for Theodore Roosevelt’s record)
• Another possible answer could be “President Theodore Roosevelt actually didn’t have a middle name. His parents never gave him a middle name, and I know for a fact that Theodore Roosevelt didn’t have a middle name.” (You would represent that by putting an empty string, or a ” into the MIDDLE_NAME column)

Oracle database is the most notable exception where those two values are actually both going to be represented by NULL–most databases other than Oracle are going to treat NULL and an empty string very differently.

As long as you can remember that a NULL value represents an unknown value, then this is going to help you craft your SQL queries, and help you work around some of the trickier situations that you can get in with NULL values.

If, for example, you were to have a query that uses a WHERE clause like this:

SELECT * FROM SOME_TABLE
WHERE 1 = 1

This query will return rows (assuming SOME_TABLE isn’t an empty table!) because the expression “1 = 1” is provably true…it can be proven to be true.

If I were to say:

SELECT * FROM SOME_TABLE
WHERE 1 = 0

…then the database sees this and evaluates “1 = 0” as false (meaning this query would never return any rows).

But if I were to say:

SELECT * FROM SOME_TABLE
WHERE 1 = NULL

The database basically goes, “I don’t know if these two values (1 and our black-box NULL value) are equal”…so it doesn’t return any records.

Ternary Logic

When you have a WHERE clause in a SQL query, it can have one of three different results:

• It can be true (and it will return rows)
• It can be false (and it won’t return rows)
• Or it can be NULL or unknown (an unknown is also not going to return values)

You may be thinking, “Okay, but why do I care that there’s a difference between false and null since the database handles those two values exactly the same?”

Well, let me show you where you can run into trouble:  Let’s introduce the NOT() condition.

If you were to say:

SELECT * FROM SOME_TABLE
WHERE NOT(1 = 1)

Then the database is first going to evaluate 1 = 1 and say, “Okay, that’s clearly true.”

But then it’s going to apply the NOT() condition to it. The database is going to go, “Well true, when notted, turns to false…so the NOT() condition causes our WHERE clause to be false here.”

So the query above isn’t going to return any records.

However, if you were to say:

SELECT * FROM SOME_TABLE
WHERE NOT(1 = 0)

Then the database first evaluates expression 1 = 0 and says, “That’s clearly false.”

But then it’s going to apply the NOT() condition, which will give us the opposite result, so it becomes true.

So this query will return records!

What if I issued the following query though?

SELECT * FROM SOME_TABLE
WHERE NOT(1 = NULL)

The database is first going to evaluate 1 = NULL. (Remember, it’s going to treat NULL like an unknown value!)

It’s going to say, “I can’t say whether or not 1 is equal to NULL because I don’t know what the NULL (unknown) value is.”

So it doesn’t yield a true result, and it doesn’t yield a false result–it instead yields a NULL (or unknown) result.

This NULL result is going to be interpreted by the NOT() operator.

Whenever you take a NULL and you put it in a NOT() condition…the result is another NULL! (the opposite of unknown is…well…another unknown).

So the NOT() operator doesn’t do anything with null conditions.

So NEITHER of these queries…

SELECT * FROM SOME_TABLE
WHERE NOT(1 = NULL)

SELECT * FROM SOME_TABLE
WHERE 1 = NULL

…is ever going to return any records…even though they’re opposites!

NULL and NOT IN

If I issued a query with a WHERE clause like this:

SELECT * FROM SOME_TABLE
WHERE 1 IN (1, 2, 3, 4, NULL)

…then clearly the WHERE clause is going to be true, and this query will return records, since 1 is in our IN list…

But if I were to say:

SELECT * FROM SOME_TABLE
WHERE 1 NOT IN (1, 2, 3, 4, NULL)

Then clearly this is going to be false, and this query will never return records, since the number 1 appears in our IN list and we’re saying “NOT IN”…

Now what if I were to say something like this?

SELECT * FROM SOME_TABLE
WHERE 5 NOT IN (1, 2, 3, 4, NULL)

This WHERE clause will never return any records, since it is not provably true (it cannot be proven to be true). The number 5 doesn’t explicitly appear in the “IN” list–But 5 could be inside our “black box” NULL value (the database doesn’t necessarily know what the value of NULL is).

This yields a NULL result (meaning an unknown result) and this WHERE clause is never going to return any records.

This is why it’s important to consider a NULL value to be equivalent to an unknown value–it’s going to help you whenever you craft complex SQL queries.

Hopefully you’re now equipped to deal with NULL values in SQL queries! For more information on SQL, Oracle database, and making queries run faster, visit blog.tuningsql.com.