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

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"
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.