Question

query to display all the records containing NULL values for price in table 'ITEM2'?​

Answer 1

Answer:

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;

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:

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 four records, 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:

Explanation: