Using SQL WHERE Clause With Comparison Operators
In this lesson we cover the types of conditions you can put in the WHERE
clause of any SQL SELECT
query. There are several combinations based on the comparison operator and the data type of the columns in the operation.
Here is the example table. Suppose you work on a farm growing apples. The database contains a single table named apple
with all the production history. The table has the following 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 |
Simple Comparison in SQL
SQL
has several comparison operators. The most simple one is the equal
operator. You can compare any data type column using the equal operator. For example:
SELECT apple_variety, tons_produced
FROM apples
WHERE year = 2019
After executing the SQL
the database returns the records where the year
column is 2019:
apple_variety | tons_produced |
---|---|
Red Delicious | 87 |
Magic Green | 26 |
Where equal
is the most used comparison operator, SQL
offers many other interesting comparison operators, including:
GREATER THAN | > |
LESS THAN | < |
GREATER THAN OR EQUAL | >= |
LESS THAN OR EQUAL. | <= |
NOT EQUAL | <> or != |
For example if you want to obtain the records in the range between 2017 and 2019 you can use the following query:
SELECT apple_variety, number_of_tress_in_production, tons_produced
FROM apples
WHERE year >= 2017 AND year <=2019
After executing the SQL the database returns this data result set:
apple_variety | number_of_tress_in_production | tons_produced |
---|---|---|
Red Delicious | 1800 | 87 |
Magic Green | 500 | 26 |
Red Delicious | 1800 | 92 |
Red Delicious | 1500 | 76.5 |
The range comparison can be applied to other data types like text strings or date columns. For example, to obtain the years where harvest started before the first summer storm:
SELECT year,
apple_variety,
number_of_tress_in_production,
tons_produced
FROM apples
WHERE harvest_day < first_summer_storm
The result is:
year | apple_variety | number_of_tress_in_production | tons_produced |
---|---|---|---|
2020 | Red Delicious | 2000 | 102 |
2020 | Magic Green | 700 | 33 |
2019 | Magic Green | 500 | 26 |
2017 | Red Delicious | 1500 | 76.5 |
You can also use the inequality operator with numeric, date or text string data type columns to exclude some records. In this example, you obtain all history production, excluding the apple variety Red Delicious.
SELECT year, apple_variety, number_of_trees_in_production,
tons_produced, harvest_day, price_per_ton
FROM apples
WHERE apple_variety <> ‘Red Delicious’
year | apple_variety | number_of_trees_in_production | tons_produced | harvest_day | price_per_ton |
---|---|---|---|---|---|
2020 | Magic Green | 700 | 33 | 06/12/2020 | 62.60 |
2019 | Magic Green | 500 | 26 | 06/28/2019 | 59.40 |
SQL WHERE with Arithmetic Operations and Tranformations
SQL
allows arithmetic calculations in the WHERE
conditions. This type of condition increases the expressive power of SQL
. For example, suppose you want to obtain those years where production was more than 50 kilograms of apples per tree. You can put an arithmetic operation to calculate the kilograms of apples produced per tree:
SELECT year, apple_variety, number_of_trees_in_production,
tons_produced,
(tons_produced * 1000 ) / number_of_trees_in_production AS kilograms_per_tree
FROM apples
WHERE (tons_produced * 1000 ) / number_of_trees_in_production > 50
The results:
year | apple_variety | number_of_trees_in_production | tons_produced | kilograms_per_tree | ||
---|---|---|---|---|---|---|
2020 | Red Delicious | 2000 | 102 | 51 | ||
2020 | Magic Green | 700 | 33 | 47.14 | ||
2019 | Red Delicious | 1800 | 87 | 48.33 | ||
2019 | Magic Green | 500 | 26 | 52 | ||
2018 | Red Delicious | 1800 | 92 | 51.11 | ||
2017 | Red Delicious | 1500 | 76.5 | 51 | ||
2016 | Red Delicious | 1500 | 72 | 48 |
You can create both conditions with arithmetic operations and conditions involving transformations to the values of the columns using text string operators, date operators or numeric operators. For example, you want to extract the month component from the harvest_day
column to obtain the years harvest started in June. The EXTRACT
function used in this query is dialect-specific (PostgreSQL) SQL dialect.
SELECT year, apple_variety, harvest_day
FROM apples
WHERE EXTRACT (month FROM harvest_day) = 6
Instead of doing an arithmetic calculation, you are doing a transformation. In other words, you are transforming a date into a month. Next, you compare it against June (where 6 is a numeric representation for June). The query results:
year | apple_variety | harvest_day |
---|---|---|
2020 | Red Delicious | 06/23/2020 |
2020 | Magic Green | 06/12/2020 |
2019 | Magic Green | 06/28/2019 |
2016 | Red Delicious | 06/26/2016 |
Closing Words
In this Lesson, we covered several comparison operators you can use in the WHERE
clause. We also explained how to create a WHERE
condition using an arithmetic operation or a value transformation.
In the next lesson of this tutorial, we cover logical operators like AND
, OR
and NOT
that you can use to create more complex WHERE clauses. Keep going, Learn SQL and increase your skills!