BETWEEN Command in SQL - sql - sql tutorial - learn sql
- The BETWEEN operator is used when the filtering criteria is a continuous range with a maximum value and a minimum value.
- It is always used in the WHERE clause.
- The BETWEEN operator selects values within a given range.
- The values can be numbers, text, or dates.
- The BETWEEN operator is inclusive: begin and end values are included.
Syntax
- The syntax for the BETWEEN operator is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2';
- This will select all rows whose column has a value between 'value1' and 'value2.'
Examples
- We use the following table for our examples.
- Table Store_Information
| Store_Name | Sales | Txn_Date |
|---|---|---|
| Alabama | 700 | March-06-2017 |
| Ohio | 1500 | March -08-2017 |
| Texas | 250 | March-09-2017 |
| Wyoming | 500 | March-09-2017 |
Example 1
- To select view all sales information between March-07-2017, and March-10-2017, we key in,
SELECT *
FROM Store_Information
WHERE Txn_Date BETWEEN ' March-07-2017' AND ' March-10-2017;
- Note that date may be stored in different formats in different databases.
- This tutorial simply choose one of the formats.
Result:
| Store_Name | Sales | Txn_Date |
|---|---|---|
| Ohio | 1500 | March -08-2017 |
| Texas | 250 | March-09-2017 |
| Wyoming | 500 | March-09-2017 |
- BETWEEN is an inclusive operator, meaning that 'value1' and 'value2' are included in the result.
- If we wish to exclude 'value1' and 'value2' but include everything in between, we need to change the query to the following:
SELECT "column_name"
FROM "table_name"
WHERE ("column_name" > 'value1')
AND ("column_name" < 'value2');
Example 2
- We can also use the BETWEEN operator to exclude a range of values by adding NOT in front of BETWEEN.
- In the above example, if we want to show all rows where the Sales column is not between 280 and 1000, we will use the following SQL:
SELECT *
FROM Store_Information
WHERE Sales NOT BETWEEN 280 and 1000;
Result:
| Store_Name | Sales | Txn_Date |
|---|---|---|
| Ohio | 1500 | March -08-2017 |
| Texas | 250 | March-09-2017 |
Exercises
- For these exercises,
- assume we have a table called User_Sales with the following data:
- Table User_Sales
| First_Name | Last_Name | Birth_Date | Gender | Join_Date | Total_Sales |
|---|---|---|---|---|---|
| Jill | Wilkes | Jan-05-1960 | F | Apr-05-2017 | 500 |
| Jamal | Santo | Jan-07-1975 | M | Apr-05-2017 | 200 |
| Casey | Healy | Oct-08-1983 | M | Apr-09-2017 | 350 |
| Sophie | Lee | Sep-20-1969 | F | Apr-09-2017 | 80 |
| Richard | Brown | Nov-20-1979 | M | Apr-15-2017 | 210 |
Interview questions for BETWEEN command in SQL
- Which of the following SQL statement is valid? (There can be more than one answer)
- SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 OR 300;
- SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 OR 300;
- SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 AND 300;
- SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 AND 300;
Answer: d
SELECT * FROM User_Sales WHERE Join_Date BETWEEN 'Apr-05-2015' AND 'Apr-10-2015';
Answer: 4 records are returned. They are,
| First_Name | Last_Name | Birth_Date | Gender | Join_Date | Total_Sales |
|---|---|---|---|---|---|
| Jill | Wilkes | Jan-05-1960 | F | Apr-05-2017 | 500 |
| Jamal | Santo | Jan-07-1975 | M | Apr-05-2017 | 200 |
| Casey | Healy | Oct-08-1983 | M | Apr-09-2017 | 350 |
| Sophie | Lee | Sep-20-1969 | F | Apr-09-2017 | 80 |
- How many records will be returned by the following query?
SELECT * FROM User_Sales WHERE Gender = 'F' OR Total_Sales BETWEEN 50 AND 100;
Answer: 3 record are returned. They are,
| First_Name | Last_Name | Birth_Date | Gender | Join_Date | Total_Sales |
|---|---|---|---|---|---|
| Jill | Wilkes | Jan-05-1960 | F | Apr-05-2017 | 500 |
| Sophie | Lee | Sep-20-1969 | F | Apr-09-2017 | 80 |
| Richard | Brown | Nov-20-1979 | M | Apr-15-2017 | 210 |