For an instructor lead, in-depth look at learning SQL click below.
Retrieving data from databases is an essential part of database management and no language makes this task more seamless than SQL (Structured Query Language). Among the variety of functions it offers to refine our searches, the BETWEEN operator holds significant importance.
Purpose of the BETWEEN Operator in SQL
The BETWEEN operator in SQL is used to filter the result set within a certain range. The values can be numbers, text, or dates. It is often used with the WHERE clause to select a value within a range of values.
Syntax
|
1 2 3 |
column|expression BETWEEN value1 AND value2; |
In this code snippet, value1 and value2 create a range and this statement will check whether the value of the column/expression lies within this range. It’s important to note that the BETWEEN operator is inclusive meaning it includes the boundary values of the range specified.
Examples of SQL code using the BETWEEN Operator
Example 1: Numbers
|
1 2 3 4 |
SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 50000; |
This SQL statement selects all the employees with the salary between 30000 and 50000, inclusive.
Example 2: Dates
|
1 2 3 4 |
SELECT * FROM Orders WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#; |
This SQL statement selects all orders with the OrderDate in the month of July 1996.
Remember, the BETWEEN operator can also be used with the logical NOT operator. If you use NOT BETWEEN, you’ll be able to retrieve records that do not fall within a specific range. This is the reverse operation of BETWEEN.
Example 3: Using NOT BETWEEN
|
1 2 3 4 |
SELECT * FROM Employees WHERE Salary NOT BETWEEN 30000 AND 50000; |
This SQL statement selects all the employees with Salary not between 30000 and 50000. Hence, this gives us the records of employees that have Salaries lower than 30000 and higher than 50000.
Mastering the BETWEEN operator, along with other SQL operators, will provide you the flexibility and precision to handle complex data retrieval tasks efficiently and effectively.