SQL NULL – What Is It & How Is It Used?

When writing in sql the NULL value becomes very useful. Technically a NULL isn’t a value, but the lack of a value. When we specify a NULL in a query we’re actually asking the databases to return records that do not have values.

Note: Do not confuse a NULL with spaces or blanks. This is NOT the same thing. A NULL is the complete lack of a value and a space is still considered a character value.

Here’s an example:

SELECT * FROM Customer WHERE City IS NULL;

Did you notice how our SQL syntax differs from a standard query with a WHERE clause? There is no equal (=) sign in our query, but instead we use the keyword ‘IS’. This is a bit of a nuance, but NULL is not a value. Therefore, we do not equate it. There are some exceptions to this in certain databases, but know that for most cases we use ‘IS NULL’ and not ‘= NULL’.

We can also look for the opposite cases where our City does not have NULL values.

SELECT * FROM Customer WHERE City IS NOT NULL;

Using the DBeaver editor, we’re able to query against the Customer table. As a result, we get company records returning NULL and representing the lack of values accordingly. SQL editors can all display this in various ways, but will look very similar to example below.

Final Thoughts – Things To Remember

The two biggest things to remember are that when writing a sql query a NULL is not an actual value and that we do not equate it. Always be sure to use ‘IS NULL’ or ‘IS NOT NULL’ in our queries. Don’t forget that they are also not the same as blanks or spaces. That’s a common misconception that is sure to cause some headaches.

Final Thoughts – Things To Remember

Related