SQL Reference - BETWEEN

Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-12-31';

Output

+--------+----------+------------+---------+-----------+--------+
| OrderID| CustomerID | OrderDate | Product | Quantity | Price  |
+--------+----------+------------+---------+-----------+--------+
|   10248  |     90     | 1996-07-04  |    5     |    12    |  20.00 |
|   10249  |     81     | 1996-07-05  |    2     |    20    |  45.00 |
...

Explanation

The SQL commands above select all records from the Orders table where OrderDate is within the range of ‘1996-07-01’ and ‘1996-12-31’, inclusive. The ellipsis in the output represents other rows fitting the criteria that are not shown.

Example

SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 10000;

Output

| emp_id | name  | salary  |
|--------|-------|---------|
| 1      | John  | 5000    |
| 2      | Sarah | 7000    |
| 3      | Peter | 9000    |

Explanation

The BETWEEN operator in SQL is used to select values within a given range. The values can be numbers, text, or dates. In the above example, the query selects all records from the employees table where the salary is between 5000 and 10000, inclusive.

Example

SELECT * FROM Employees
WHERE hire_date BETWEEN '2001-01-01' AND '2002-12-31';

Output

emp_no | first_name |last_name | hire_date
-------------------------------------------
 10001 |  Nancy     |   Whelan | 2001-06-23
 10002 |   Simon    |   Raghunathan | 2001-08-03
 10003 |   Joyce    |   Preusig | 2002-07-28

Explanation

The BETWEEN operator in the above SQL command is used to select all columns from the Employees table where the hire_date lies between ‘2001-01-01’ and ‘2002-12-31’. The output demonstrates the results of this query, showing only the employees who were hired in the years 2001 and 2002.

Example

SELECT *
FROM employees
WHERE salary BETWEEN 30000 AND 40000;

Output

| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL         | SALARY |
|-------------|------------|-----------|---------------|--------|
| 100         | Steven     | King      | SKING         | 35000  |
| 101         | Neena      | Kochhar   | NKOTHAR       | 37000  |

(2 rows)

Explanation

The example SQL query retrieves all the rows from the ‘employees’ table where the ‘salary’ column value falls between 30000 and 40000 (both values inclusive).

Example

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2020-01-01' AND '2020-12-31';

Output

OrderID | CustomerID | OrderDate
--------|------------|-----------
1       |    123     | 2020-02-11
2       |    456     | 2020-04-22
3       |    789     | 2020-08-15
...

Explanation

In the given example, the BETWEEN keyword is used to select all records from the Orders table where OrderDate is between the dates ‘2020-01-01’ and ‘2020-12-31’, inclusive. The output shows a subset of the returned rows, displaying the OrderID, CustomerID, and OrderDate.