In this lesson, you will learn how to write SQL BETWEEN statements.
What is a SQL BETWEEN Statement
The SQL BETWEEN operator can be used in conjunction with the WHERE clause to match a value against a low and high range of values.
Here is a generalized example of how you would use the BETWEEN operator in a SQL query:
SELECT column_name FROM table_name WHERE column_name BETWEEN low AND high;
There are many real-world scenarios in which you would benefit from using the SQL BETWEEN operator.
As an example, you could use the BETWEEN operators in our DVD Rental database to search for movies with a
replacement_cost between $5 and $10.
You would do this with the following query:
SELECT title FROM film WHERE replacement_cost BETWEEN 5 AND 10;
The SQL BETWEEN operator is inclusive, which means that it actually includes both the low and high values. Using our earlier
replacement_cost example, the SQL BETWEEN would actually include movies whose replacement cost was exactly $5 or exactly $10.
The BETWEEN operator can also be inverted using the NOT operator to find values that are outside of the high and low values. A generalized example of this is below:
SELECT column_name FROM table_name WHERE column_name NOT BETWEEN low AND high;
To go back to our
replacement_cost example once again, we could search for movie titles whose replacement cost is either less than $5 or greater than $10 with the following query:
SELECT title FROM film WHERE replacement_cost NOT BETWEEN 5 AND 10;
The Differences Between SQL WHERE and SQL BETWEEN
You may have noticed that you can structure a BETWEEN statement without actually using the BETWEEN keyword by being smart about how you construct your WHERE statement.
As an example, the following two SQL queries will return the exact same output:
SELECT title FROM film WHERE replacement_cost BETWEEN 5 AND 10; SELECT title FROM film WHERE replacement_cost >= 5 AND replacemet_cost <= 10;
In cases where the output is exactly the same, the BETWEEN operator is preferable because it makes your code easier to read.
With that said, there are still cases where multiple WHERE statements are useful.
To provide just one example, you could eliminate the inclusivity of the BETWEEN statement (meaning that the query will not return values that are directly equal to the
high value) using the following WHERE statement (notice the slightly different comparison operators):
SELECT title FROM film WHERE replacement_cost > 5 AND replacemet_cost < 10;
In this lesson, you learned how to use the BETWEEN statement to select rows whose value in a certain column is between two endpoints. In the next lesson of this SQL course, we will discuss SQL LIKE and ILIKE statements.