How To Write SQL BETWEEN Statements

Hey - Nick here! This page is a free excerpt from my $99 course SQL Fundamentals.

If you want the full course, click here to sign up and create an account.

I have a 30-day satisfaction guarantee, so there's no risk (and a ton of upside!) in signing up for this course and leveling up your SQL skills today!

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 &lt;= 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 low and high value) using the following WHERE statement (notice the slightly different comparison operators):

SELECT title

FROM film

WHERE replacement_cost > 5 AND replacemet_cost &lt; 10;

Final Thoughts

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.