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

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:

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:

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:

Exercises (Continued from previous section)

Exercise 9

Using the patient, description, and reasondescription columns in the medications table, write a query that returns all the records where a patient had a Stroke and was treated with Clopidogrel 75 MG Oral Tablet sorted by patient.

Try query

Exercise 10

Using the description column in the conditions table, write a query that returns all the data for patients who are diabetic (Diabetes) or predibetic (Prediabetes).

Try query

Exercise 11

Using the description and reasondescription columns in the table procedures, write a query that returns a list of all the descriptions for a normal pregnancy (Normal pregnancy) except a pregnancy test (Standard pregnancy test).

Try query