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.


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,
  FROM austin_animal_center_outcomes
 WHERE animal_type = "Cat"
       AND outcome_type = "Return to Owner"

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


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,
  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


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

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


SELECT the patient, description, and reasondescription columns FROM the table medications WHERE the reasondescription = “Stroke” AND the description = “Clopidogrel 75 MG Oral Tablet” ORDER the results BY the patient column.

See solution

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


SELECT all the records FROM the table conditions WHERE the description column = "Diabetes" OR the description column = "Prediabetes”

See solution

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


SELECT the column description FROM the table procedures WHERE the reasondescription = “Normal pregnancy” AND the description != “Standard pregnancy test”.

See solution