IS NULL and IS NOT NULL

An introduction to the expressions IS NULL and IS NOT NULL.

The expressions IS NULL and IS NOT NULL are used in WHEREclauses to either display rows for which there is no value in a specified column or to show only those rows which have a value in the specified column.

In the outcome table of our dataset there is the column outcome_type that contains the different outcomes for the animals at the center. Some of the possible outcomes are further defined by subtypes captured in the outcome_subtype field. If we wanted to return only data that has both an outcome type and an outcome subtype we could write the query thus:

SELECT monthyear,
       animal_type,
       outcome_type,
       outcome_subtype
  FROM austin_animal_center_outcomes
 WHERE outcome_subtype IS NOT NULL

and the first several rows returned would look like this:

monthyear animal_type outcome_type outcome_subtype
2014-01 Dog Euthanasia Rabies Risk
2014-01 Cat Euthanasia Suffering
2014-02 Dog Adoption Foster
2014-02 Dog Transfer Partner
2014-02 Dog Transfer Partner
2014-01 Dog Transfer Partner
2014-01 Cat Transfer SCRP
2014-02 Cat Transfer Partner

Exercises (Continued from previous section)

The exercises below can be run from the “Try query” link below, but they cannot be saved. If you would like to save your queries, see this article for instructions. You can also do the exercises from a complete stand-alone tutorial located here.

Exercise 14

Using the patient, description, and reasondescription columns from the medications table, write a query that returns the records where reasondescription is not null.

Try query

Hint

SELECT the columns patient, description, and reasondescription FROM the medications table WHERE the reasondescription column IS NOT NULL.

See solution