AND, OR, and NOT

An introduction to the keywords AND, OR and NOT.

While there are certainly queries that you’d like to run with only one condition restricting the data returned, there are likely many more that require combining conditions to return exactly the results you want. AND, OR, and NOT are the logical operators SQL uses to combine conditions in WHERE clauses in order to refine the data returned by a query.

AND

The first of the logical operators is AND. AND is used in the WHERE clause of a query to return all the rows which meet all of the conditions specified that are joined with AND. If we wanted to see all of the cats who were returned to their owners, we could write a query like this:

SELECT monthyear,
       name,
       age_upon_outcome
  FROM austin_animal_center_outcomes
 WHERE animal_type = "Cat"
       AND outcome_type = "Return to Owner"
 ORDER BY name DESC
Notice that we did not choose to include the fields used with the AND operator in the WHERE clause. As the query only returns rows for “Cat” and “Return to owner”, the data in those columns would be redundant because it would all be the same. Also note that because we included an ORDER BY clause, the data returned was sorted by the cats’ names in descending order.
monthyear name age_upon_outcome
2014-12 Zulma 4 years
2017-08 Zorro 2 years
2016-07 Zoey 7 months
2016-11 Zoey 12 years
2014-11 Zoe 3 years
2016-03 Zoe 6 months
2017-09 “Zippy #Z16” 1 year
2016-09 Zion 6 years
2014-02 Zillah 15 years
2014-03 Zillah 15 years
2016-05 Ziggy 14 years
2017-06 Ziggy 5 years
2014-03 Zena 11 months
2016-01 Zelda 8 months
2015-04 Zeke 13 years

OR

Another way to restrict the data returned form a query is to use the logical operation OR. OR returns all the rows in a dataset that meet the first condition specified before the OR as well as all data that meets the second condition specified after the OR. Let’s say we wanted to see all of the cats and dogs who were either strays or owner surrenders. We could write the query like this:

SELECT animal_type,
       intake_type,
       Intake_condition,
       age_upon_intake
  FROM austin_animal_center_intakes
 WHERE (animal_type = "Cat" OR animal_type = "DOG")
       AND (intake_type = "Stray" OR intake_type = "Owner Surrender")

The first several rows in the table returned would be:

animal_type intake_type Intake_condition age_upon_intake
Cat Stray Normal 16 years
Cat Stray Normal 1 month
Cat Owner Surrender Normal 10 years
Cat Owner Surrender Normal 9 months
Cat Stray Normal 10 months
Cat Owner Surrender Sick 15 years
Cat Stray Normal 7 years

NOT

The last logical operation used in the WHERE clause is NOT which is most commonly used with other keywords such as BETWEEN, LIKE or IN. However it can also be used to negate an entire section in the WHERE clause. Let’s say we already had a query that returned all the rows for cats and dogs who were taken in who were sick. That query might look like this:

SELECT monthyear,
       animal_type,
       intake_type,
       intake_condition
  FROM austin_animal_center_intakes
 WHERE ((animal_type = "Cat") OR (animal_type = "Dog"))
       AND (intake_condition = "Sick")

And the first several rows of the data returned would look like this:

monthyear animal_type intake_type Intake_condition
2014-02 Cat Owner Surrender Sick
2014-02 Cat Owner Surrender Sick
2014-02 Cat Stray Sick
2014-02 Cat Stray Sick
2014-02 Cat Stray Sick
2014-03 Cat Stray Sick
2014-03 Cat Stray Sick
2014-03 Cat Owner Surrender Sick

If for some reason you wanted to see all of the data for animals that were neither sick cats nor sick dogs, you could add NOT to the WHERE clause:

SELECT monthyear,
       animal_type,
       intake_type,
       Intake_condition
  FROM austin_animal_center_intakes
 WHERE NOT (((animal_type = "Cat") OR (animal_type = "Dog"))
       AND (intake_condition = "Sick"))

The table returned from this query would begin like:

monthyear animal_type intake_type Intake_condition
2017-12 Dog Stray Normal
2014-12 Dog Public Assist Normal
2015-11 Cat Stray Normal
2015-03 Dog Public Assist Normal
2016-03 Dog Public Assist Normal
2014-02 Dog Owner Surrender Normal
2014-02 Dog Public Assist Normal
The example above is cumbersome and there are other ways to write the query which would be easier for the eye to parse. NOT is not a commonly used logical operator unless combined with LIKE, BETWEEN, etc.