Using Logical Operators in WHERE clause
In this lesson we cover more complex filters than those you learned in Using The WHERE Clause With Comparison Operators by using logical operators like AND
, OR
and NOT
.
In these example queries, we continue using the apple
table used in the previous lesson. As a reminder, apple
is a single table database with all the production history for an apple farm. Here is the schema:
Year | Apple Variety | Number of Trees in Production | Tons Produced | Harvest day | Price per Ton | First summer storm |
---|---|---|---|---|---|---|
2020 | Red Delicious | 2000 | 102 | 06/23/2020 | 54.50 | 07/03/2020 |
2020 | Magic Green | 700 | 33 | 06/12/2020 | 62.60 | 07/03/2020 |
2019 | Red Delicious | 1800 | 87 | 07/15/2019 | 52.25 | 07/12/2019 |
2019 | Magic Green | 500 | 26 | 06/28/2019 | 59.40 | 07/12/2019 |
2018 | Red Delicious | 1800 | 92 | 07/02/2018 | 56.75 | 06/03/2018 |
2017 | Red Delicious | 1500 | 76.5 | 07/18/2017 | 51.45 | 07/30/2017 |
2016 | Red Delicious | 1500 | 72 | 06/26/2016 | 47.60 | 06/23/2016 |
Logical Operators
The most used logical operator is the AND
, used to filter records that satisfy two conditions simultaneously. For example, to obtain the years where you harvest more than 90 Tons of Red Delicious variety, you have two conditions: Tons_produced > 90
and variety = 'Red Delicious'
. To obtain the records satisfying both conditions, you connect them with the AND
logical operator:
SELECT year, apple_variety, tons_produced
FROM apples
WHERE apple_variety = ‘Red Delicious’
AND Tons_Produced > 90
Here are the results:
year | apple_variety | tons_produced |
---|---|---|
2020 | Red Delicious | 102 |
2018 | Red Delicious | 92 |
The logical OR
operator is used to filter records if any of the conditions separated by the OR
is TRUE
. For example, suppose for an Red Delicious
grower a good year is one where either more than 50 kilos per tree are harvested or where the price per ton is greater than $54. Use this query to obtain those years:
SELECT year,
apple_variety,
number_of_trees_in_production,
tons_produced,
price_per_ton,
(tons_produced * 1000 ) / number_of_trees_in_production AS kg_per_tree
FROM apples
WHERE apple_variety = ‘Red Delicious’
AND (
price_per_ton > 54
OR
(tons_produced * 1000 ) / number_of_trees_in_production > 50
)
Here is the result:
year | apple_variety | number_of_trees_in_production | tons_produced | price_per_ton | kg_per_tree |
---|---|---|---|---|---|
2020 | Red Delicious | 2000 | 102 | 54.50 | 51 |
2018 | Red Delicious | 1800 | 92 | 56.75 | 51.11 |
2017 | Red Delicious | 1500 | 76.5 | 51.45 | 51 |
The NOT
logical operator negates a WHERE condition, enabling you to select rows that do not match the condition. The NOT
operator is frequently used with subqueries.
In this example, you convert the good year query to obtain the bad years for a Red Delicious
growers:
SELECT year,
apple_variety,
number_of_trees_in_production,
tons_produced,
price_per_ton,
(tons_produced * 1000 ) / number_of_trees_in_production AS kg_per_tree
FROM apples
WHERE variety = ‘Red Delicious’
AND NOT (
price_per_ton > 54
OR
(tons_produced * 1000 ) / number_of_trees_in_production > 50
)
Here is the bad years result, obtained with the NOT
operator applied to the good year definition.
year | apple_variety | number_of_trees_in_production | tons_produced | price_per_ton | kg_per_tree |
---|---|---|---|---|---|
2019 | Red Delicious | 1800 | 87 | 52.25 | 48.33 |
2016 | Red Delicious | 1500 | 72 | 47.60 | 48 |
Reader Exercise: Can you create an equivalent WHERE
condition avoiding the NOT
operator to obtain the same bad year results?
Closing Words
In this section of the tutorial we covered the logical operators AND
, OR
and NOT
. Using the logical operators of the current lesson and the comparison operators of the previous lesson, you can create very complex WHERE
clauses. There are many more new concepts to learn about WHERE
clauses. Keep going, Learn SQL and increase your skills!