The BETWEEN operator in SQL is used to filter records within a certain range. It allows you to specify a lower and upper boundary, returning values that fall within this interval. This operator can be applied to various data types, including numeric, date, and text fields. The syntax generally follows this structure: column_name BETWEEN value1 AND value2. It’s important to note that both endpoints are inclusive, meaning the records that exactly match either value will be included in the results.
For instance, if you have a table named employees that contains a column called salary, and you want to find all employees with salaries between $50,000 and $70,000, you would write your SQL query like this:
SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 70000;
This query will return all records where the salary is greater than or equal to $50,000 and less than or equal to $70,000. You can also use the BETWEEN operator with date ranges. For example, if you wish to retrieve records of employees hired between January 1, 2020, and December 31, 2021, the query would look like this:
SELECT * FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';
While the BETWEEN operator is convenient, it is essential to consider the data types you are working with to avoid unexpected results. For example, when dealing with text, the operator performs a lexical comparison, so "apple" is less than "banana". Additionally, if you need to include only specific endpoints (exclusive) in your search criteria, you can use the greater than (>) and less than (<) operators instead. Understanding how to use the BETWEEN operator effectively can help you simplify your queries and make your SQL statements more efficient.