AND, OR, and NOT
An introduction to the keywords
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.
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 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.
|2017-09||“Zippy #Z16”||1 year|
Another way to restrict the data returned form a query is to use the logical operation
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:
|Cat||Owner Surrender||Normal||10 years|
|Cat||Owner Surrender||Normal||9 months|
|Cat||Owner Surrender||Sick||15 years|
The last logical operation used in the
WHERE clause is
NOT which is most commonly used with other keywords such as
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
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:
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
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.
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.
FROM the table
“Clopidogrel 75 MG Oral Tablet”
ORDER the results
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).
SELECT all the records
FROM the table
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).
SELECT the column
FROM the table
“Standard pregnancy test”.
An introduction to the arithmetic operators