SQL BETWEEN and SQL LIKE in WHERE Clause

SQL Tutorial Home

In this lesson we explore more complex filtering operators than those covered in the previous lessons: SQL logical operators and SQL comparison operators.

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
2020 Red Globus 500 26 05/30/2020 71.50 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
2019 Red Globus 500 27 05/28/2019 68.00 07/12/2019
2018 Red Delicious 1800 92 07/02/2018 56.75 06/03/2018
2018 Red Globus 500 24 05/30/2018 66.00 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

SQL LIKE Operator and SQL Wildcards

The SQL LIKE operator is used to create simple text searches. It is used in conjunction with two SQL wildcard characters:

‘%’ matches with any string with zero, one or multiple characters

‘_’ matches with any single character

Here are some examples of LIKE expressions:
LIKE Expression name LIKE ‘John%’
Name Value ‘John Doe’
Value returned by LIKE TRUE
Description matches with any text starting with John
LIKE Expression name LIKE ‘%John%’
Name Value ‘John Doe’
Value returned by LIKE TRUE
Description matches with any text including John anywhere
LIKE Expression name LIKE ‘%John’
Name Value ‘John Doe’
Value returned by LIKE FALSE
Description matches with any text ending with ‘John’
LIKE Expression name LIKE ‘_arl%’
Name Value ‘Karl Smith’
Value returned by LIKE TRUE
Description matches with any text starting with any character followed by arl
LIKE Expression name LIKE ‘_arl’
Name Value ‘Karl Smith’
Value returned by LIKE FALSE
Description matches with any text of 4 characters ending with arl
LIKE Expression name LIKE ‘_ar_’
Name Value ‘Carl’
Value returned by LIKE TRUE
Description any text of 4 characters with ar in second and third position.
LIKE Expression name LIKE ‘J%n’
Name Value Jonnathan
Value returned by LIKE TRUE
Description matches with any name starting with J and ending with n. anything in the middle.

This SQL query uses the SQL LIKE operator to obtain all records containing the word Red in the variety name.


SELECT year, apple_variety, tons_produced
FROM   apples
WHERE  apple_variety LIKE ‘%Red%’

Here are the results:

year apple_variety tons_produced
2020 Red Delicious 102
2020 Red Globus 26
2019 Red Delicious 87
2019 Red Globus 27
2018 Red Delicious 92
2018 Red Globus 24
2017 Red Delicious 76.5
2016 Red Delicious 72

SQL BETWEEN Operator

The SQL BETWEEN operator is used to obtain records with any column having a value in a given range. For example, here is a query to obtain the years where price was in the range of 50 to 60 dollars:


SELECT year, 
       apple_variety,
       price_per_ton
FROM   apples
WHERE  price_per_ton BETWEEN 50 and 60

Here is the result:

year apple_variety price_per_ton
2020 Red Delicious 54.50
2019 Red Delicious 52.25
2019 Magic Green 59.40
2018 Red Delicious 56.75
2017 Red Delicious 51.45

The SQL BETWEEN operator can be used with other data types like text strings or dates. In this query, you obtain the records in which the harvest day was in the 30 days period before the first_summer_storm:


SELECT year, 
       apple_variety,
       Tons_Produced,
       Harvest_day,
       First_summer_storm
FROM   apples
WHERE  harvest_day BETWEEN first_summer_storm - interval ‘30’ days AND first_summer_storm
Calculating a date by subtracting an interval (like first_summer_storm - interval ‘30’ days) is allowed in the PostgresSQL dialect. It may not be available in the SQL dialect you are using.

Here is the result:

year apple_variety Tons_Produced Harvest_day First_summer_storm
2020 Red Delicious 102 06/23/2020 07/03/2020
2020 Magic Green 33 06/12/2020 07/03/2020
2019 Magic Green 26 06/28/2019 07/12/2019
2018 Red Globus 24 05/30/2018 06/03/2018
2017 Red Delicious 76.5 07/18/2017 07/30/2017

Closing Words

In this lesson we covered two new SQL operators: LIKE and BETWEEN. Adding these two new operators to the comparison and logical operators of the previous lessons enable you to 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