While most applications will have some form of server-side or even client-side technology that can be used to filter out database query results which may contain
NULL or empty values, using another language and that additional burden of executing code is typically more costly for the server and, in fact, largely unnecessary.
Instead, below we’ll briefly explore how to use the MySQL engine itself to perform this task, only grabbing records that you desire while excluding those with pesky
NULL columns in the mix.
IS NOT NULL Comparison Operator
By far the simplest and most straightforward method for ensuring a particular column’s result set doesn’t contain
NULL values is to use the
IS NOT NULL comparison operator.
For example, if we want to select all records in our
books table where the
primary_author column is not
NULL, the query might look like this:
SELECT primary_author, published_date, title FROM books WHERE primary_author IS NOT NULL;
IS NOT NULL behaves just like any other comparator, it can be combined with other
WHERE clauses to further filter results, just as if you were using
LIKE and other standard comparison operators.
Filtering NULL from Multiple Columns
In some cases you may desire to retrieve results where no
NULL values are present across multiple columns. For example, we have some records in the
id | title | primary_author | published_date 1 | The Hobbit | Tolkien | 09-21-1937 2 | The Silmarillion | Tolkien | 09-15-1977 3 | The Name of the Wind | Rothfuss | 03-27-2007 4 | The Wise Man's Fear | Rothfuss | 03-01-2011 5 | The Doors of Stone | Rothfuss | NULL 6 | Beowulf | NULL | 01-01-1000
Take note that The Doors of Stone (
id 5) is unpublished and therefore the
NULL. Similarly, Beowulf (
id 6) – the famous Old English epic poem – has no known author, so
In this case, we may want to query for results containing only the first four records, thereby excluding the final two records which have
NULL values in either
published_date. This can be performed with this simple statement using
AND with multiple comparison operators:
SELECT primary_author, published_date, title FROM books WHERE ( primary_author IS NOT NULL AND published_date IS NOT NULL );
Note: The parentheses are not required but it’s good practice to enclose grouped comparators for better readability.
There you have it; a simple comparison operator that can be used to filter out any and all
NULL values in your records.