What are NULL values?


#1

Question

What are NULL values, and how do I return them in my query?

Answer

In SQL, NULL represents a field with missing data.

Since NULL has no value, it cannot be compared using comparison operators such as != (since no value is actually “equal” or “unequal” to it).

For example, let’s say the response column in our table below contains either 'Y', 'N', or NULL (ie. for missing response). The following query would only return rows where response equals to 'Y'. Rows where response is NULL would not be returned:

SELECT *
FROM tbl
WHERE response != 'N';

We can only use IS NULL or IS NOT NULL to deal directly with NULL values. The below query will return both rows where response equals to 'Y' and rows where response is NULL:

SELECT *
FROM tbl
WHERE response != 'N'
OR response IS NULL;

You can read more about NULL here!