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:

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:

Where equal is the most used comparison operator, SQL offers many other interesting comparison operators, including:

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:

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:

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’

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:

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:

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!