Concepts/Basic/

WHERE and Comparison Operators

An introduction to the WHERE clause and comparison operators.

One way to restrict the data presented from a query is to use a WHERE clause. WHERE is used to return data that matches a specified condition and is combined with the comparison operators =, !=, >, <, >=, and <=.

Let’s say we wanted to know all the months where more than 1,000 of one type of animal was taken in. We could run a query like this one:

 SELECT year,
        month,
        animal_type,
        count
   FROM austin_animal_center_intakes_by_month
  WHERE COUNT > 1000
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code

and we would get these results:

year month animal_type count
2016 5 Dog 1,020
2015 5 Cat 1,009
2015 6 Cat 1,103
2015 6 Dog 1,014

You can also use a WHEREclause with = and != to limit results for text fields. E.g., if we wanted only to look at information about cats taken in by the Austin Animal Center we could run this query:

SELECT year,
       month,
       count,
       animal_type
  FROM austin_animal_center_intakes_by_month
 WHERE animal_type = "Cat"
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code

Notice that for numeric values, the number you compare (1000 in the first example) is not bounded by quotes. When you compare text strings, the compared text must be enclosed in double quotes (“Cat” in the second example). Queries involving text strings are also case sensitive. If we run the second query with “cat” instead of “Cat” we would get a message that our query returned no results.

The first seven rows returned would look like this:

year month count animal_type
2013 10 542 Cat
2015 11 488 Cat
2015 12 320 Cat
2016 1 304 Cat
2016 2 279 Cat
2014 1 335 Cat
2016 3 333 Cat

While it is legal to run queries using the >, <, >=, and <= against columns which contain strings of text, the results might not be what you expect. Text strings are treated like numbers with alphabetic order determining which is greater than and which is less than. If we were to run a query against the Intakes table for all animal types > “Cat” the results would include all Dogs, Livestock, Other, and Wildlife, but no cats or birds.

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 5

Using the description column in the allergies table, write a query that returns all of the patients with an allergy to mould.

Try query

Hint

SELECT all the records FROM the allergies table WHERE the description = “Allergy to mould”

See solution

Exercise 6

Using the column occurrences from the all_prevalences table, write a query that shows all of the conditions that show up more than 100 times in your data.

Try query

Hint

SELECT all the records FROM the all_prevalences table WHERE the occurrences are > 100

See solution