SQL BETWEEN Operator
The SQL BETWEEN operator is used to filter results within a specific range of values. It is typically used in the WHERE clause of a SQL query. The syntax for the BETWEEN operator is:
|
1 2 3 |
SELECT column1, column2, ... FROM table_name WHERE column_name BETWEEN value1 AND value2; |
column_name: The column you want to filter.value1andvalue2: The lower and upper bounds of the range, respectively. These values can be numbers, text, or dates depending on the data type of the column.
The BETWEEN operator is inclusive, meaning it includes both value1 and value2 in the result set. If you want to exclude one of the boundary values, you can use other comparison operators (
,<,>=,<=) combined with logical operators (AND,OR).
Here’s an example to illustrate how to use the BETWEEN operator:
Let’s say we have a table named employees with columns employee_id, first_name, last_name, and salary, and we want to retrieve employees whose salaries fall within a certain range, for example, between $30,000 and $50,000:
|
1 2 3 |
SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary BETWEEN 30000 AND 50000; |
This query will return all rows from the employees table where the salary column falls within the range of $30,000 to $50,000, inclusive.
Keep in mind that the BETWEEN operator can be used with different data types such as numbers, text, or dates, as long as the values being compared are of compatible types.
Here’s the structure of the students table:
| student_id | student_name | age | grade |
|---|---|---|---|
| 1 | Alice | 18 | A |
| 2 | Bob | 20 | B |
| 3 | Charlie | 16 | C |
| 4 | David | 19 | B |
| 5 | Emma | 17 | A |
Let’s say we want to find students between the ages of 17 and 19, inclusive.
Here’s how you would use the BETWEEN operator to achieve this:
|
1 2 3 |
SELECT student_name, age, grade FROM students WHERE age BETWEEN 17 AND 19; |
Explanation:
- We are selecting the
student_name,age, andgradecolumns from thestudentstable. - We’re using the
WHEREclause to specify the condition that theagecolumn should be between 17 and 19. - The
BETWEENoperator includes both 17 and 19 in the result set. So, students who are exactly 17 or 19 years old will also be included.
When you execute this query, you’ll get the following result:
| student_name | age | grade |
|---|---|---|
| Alice | 18 | A |
| Bob | 20 | B |
| David | 19 | B |
| Emma | 17 | A |
As you can see, the query retrieves all students whose ages are between 17 and 19, inclusive. The BETWEEN operator makes it easy to specify a range condition without having to use multiple comparison operators.
