How to SELECT Records With No NULL Values in MySQL

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 NULLcomparison 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;

Since 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 books table:

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 published_date is NULL. Similarly, Beowulf (id 6) – the famous Old English epic poem – has no known author, so primary_author is NULL.

In this case, we may want to query for results containing only the first fourrecords, thereby excluding the final two records which have NULL values in either primary_author or 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.