SQL Basics
Querying Data
Filtering Data
Joining Tables
SQL Functions
Modifying Data
Defining Data
SQL BETWEEN
In this tutorial, you will learn how to check if a value falls within a specific range using the SQL operator BETWEEN within the WHERE clause.
The BETWEEN is one of the logical operators in SQL that allows you to select only rows that are within a specific range.
| AND | Return True if both conditions are True |
| OR | Return True if one of conditions is True |
| NOT | Reverse the result of any Boolean operator |
| IN | Return True if a value is in a set of values |
| BETWEEN | Return True if a value falls within a specific range |
| LIKE | Return True if a value matches a pattern |
The BETWEEN operator returns TRUE if the value is greater than or equal (>=) to the min value and less than or equal (<=) to the max value.
Syntax
The basic syntax of the BETWEEN operator to select values within a given range can be given with:
SELECT column_names
FROM table_name
WHERE column_name BETWEEN min_value AND max_value
- The values used in BETWEEN operator can be numbers, text, or dates.
- The values used in BETWEEN operator are included in the condition.
Technically, the BETWEEN is the equivalent to the following Syntax that uses the operators: (>=) and (<=)
SELECT column_names
FROM table_name
WHERE column_name >= min_value AND column_name <= max_value
BETWEEN Numeric Example
To understand the BETWEEN statement in a better way, let’s look at the following customers table in our tutorial database:
| customer_id | first_name | last_name | country | score |
|---|---|---|---|---|
| 1 | Maria | Cramer | Germany | 350 |
| 2 | John | Steel | USA | 900 |
| 3 | Georg | Pipps | UK | 750 |
| 4 | Martin | Müller | Germany | 500 |
| 5 | Peter | Franken | USA | NULL |
Now, let’s check out some examples that demonstrate how it actually works.
We have the following task be to solve using SQL statements
The following SQL statement will returns all customers from customers table and order the result by the score column in ascending order.
SELECT *
FROM customers
WHERE score BETWEEN 100 AND 500
After executing the above query, you’ll get the result set something like this:
As you can see the output contains everything the whole customers tables including all rows and columns.
SELECT
*
FROM customers
WHERE score >= 100 AND score <= 500
Similarly, you can use the DESC option to perform a sorting in descending order. The following statement will orders the result set by the numeric salary column in descending order.
Some people prefer not using e BETWEEN and use the second variant like the query above, because it more explicitly shows what the query is doing (it’s easy to forget whether or not e BETWEEN includes the range bounds)
BETWEEN Date Example
As you can see the output contains everything the whole customers tables including all rows and columns.
SELECT *
FROM orders
WHERE order_date between '2021-01-01' AND '2021-04-30'
