The SQL BETWEEN statement allows you to select all records in between two values.
How Does SQL Between Work?
Like the SQL IN keyword, SQL BETWEEN is placed inside the WHERE statement. Unlike SQL IN, which is akin to a list of OR operators, SQL BETWEEN is more like an AND operator.
SQL Between Statement Example
Here's how to use SQL BETWEEN to see payments you've received that are BETWEEN $3 and $5, using the MySQL Sakila demo database.
SELECT COUNT(payment_id) AS num_payments
FROM payment
WHERE amount BETWEEN 3 AND 5;
The SQL BETWEEN query above is functionally equivalent to the SQL AND query below:
SELECT COUNT(payment_id) AS num_payments
FROM payment
WHERE amount >= 3 AND amount <= 5;
Note: It is possible to chain three or more values joined with AND after the BETWEEN operator. However, since BETWEEN works on a range, it usually doesn't make sense to have more than two values.
Summary: SQL Between Statement
- The SQL
BETWEENoperator returns all values that are between specific values - Using the SQL
BETWEENoperator is equivalent to an expression using theANDoperator - SQL
BETWEENis placed inside theWHEREclause, traditionally between two values and followed by a ;
SQL Between Syntax
Here's how you write the SQL BETWEEN statement, where you can substitute the variables in < > with your values.
SELECT <YOUR_COLUMNS>
FROM <YOUR_TABLE>
WHERE <YOUR_COLUMN> BETWEEN <YOUR_VALUE_1> AND <YOUR_VALUE_2>