Using Logical Operators in WHERE clause

CONTENTS

SQL Tutorial Home

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!

IN THIS PAGE