Concepts/Basic/

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
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code

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)

There are two ways to do these exercises. The first is to use the “Try query” links to test your queries without saving them. The second is to create a data.world project and save your queries to it. If you are reading this documentation and completing the exercises as a tutorial, you will need to create your own project to save your work. Details and instructions are in the SQL tutorial which has instructions for setting up your project and links to all the current exercises.

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