This 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. By Mitchum.
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?
SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN IS NULL
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).
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
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. Good read.
[Read More]