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
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|
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:
OR operator is used to filter records if any of the conditions separated by the
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:
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.
Reader Exercise: Can you create an equivalent
WHERE condition avoiding the
NOT operator to obtain the same bad year results?
In this section of the tutorial we covered the logical operators
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