How to Use the SQL BETWEEN Operator

The SQL BETWEEN operator is a tool for filtering values within a particular range. It is included in the SELECT statement to simplify and improve query readability.

If you are new to SQL, I encourage you to take Introduction to SQL and SQL Fundamentals courses to kickstart your learning of SQL and become a skilled analyst.

The Short Answer: What is the SQL BETWEEN Operator?

The BETWEEN operator filters and returns rows in a dataset within a certain range. When included in the SELECT statement, the BETWEEN operator returns the given start and end values. This powerful operator can return values within various SQL data types, including dates, numeric, and string data.

In the example below, the query is used to select all employee records whose salaries are between 50,000 and 60,000 from the employees table. Remember that the BETWEEN operator is inclusive, so 50,000 and 60,000 will both be included in the result.

-- Select all columns from the Employees table
SELECT * 
FROM employees
-- Filter the results to include only employees with salaries between 50,000 and 60,000
WHERE salary BETWEEN 50000 AND 60000;

Syntax of the SQL BETWEEN Operator

The BETWEEN operator has simple syntax in the SELECT statement to filter the values. It is used within the WHERE clause to specify the lower and upper bounds of the data range being filtered.

-- Select the specified columns from the table
SELECT column_name(s)
FROM table_name
-- Filter the results to include only those where column_name values fall between (inclusive)
WHERE column_name BETWEEN value1 AND value2;

DataCamp’s SQL Basics Cheat Sheet will also come in handy if you want to reference the syntax of common operators, including BETWEEN.

Important Points to Remember About SQL BETWEEN

When using the BETWEEN operator, it is important to note that it is placed in the WHERE clause of the SELECT statement. This placement ensures the BETWEEN operator filters the column highlighted by the WHERE clause.

-- Select all columns from the Employees table
SELECT * 
FROM employees
-- Filter the results to include only employees with employeee_id between 10 and 18
WHERE employee_id BETWEEN 10 AND 18;

The BETWEEN operator is preferred for filtering values within ranges since it provides concise data filtering. The syntax is simple and improves query readability when filtering multiple conditions. 

Without the BETWEEN operator, we use other operators to achieve similar results. Note the inclusion of the operators in the WHERE clause, which makes the query less readable and complex.

-- Select the first name, last name, and salary columns from the Employees table
SELECT first_name, last_name, salary
FROM employees
-- Filter the results to include only those employees whose salary is greater than or equal to 50,000
-- and less than or equal to 60,000
WHERE salary >= 50000 AND salary <= 60000;

The query is more readable and easy to understand with the BETWEEN operator.

-- Select the first name, last name, and salary columns from the Employees table
SELECT first_name, last_name, salary
FROM employees
-- Filter the results to include only those employees whose salary is between 50,000 and 60,000 (inclusive)
WHERE salary BETWEEN 50000 AND 60000;

Always remember that the values specified in the BETWEEN operator are included in the results. The SQL query will return records where the values are included as upper and lower bounds of the data.

Common Use Cases of SQL BETWEEN

The BETWEEN operator has different use cases when filtering data, including filtering numeric or date ranges. Let's take a look.

Filtering numeric ranges with BETWEEN

The BETWEEN operator is commonly used to filter numeric data. In this scenario, you can filter data to retrieve those within a certain range. In the example below, we filter the employees table to return rows with salaries between 62,000 and 70,000.

-- Select all columns from employees table
SELECT *
FROM employees
-- Filter the results to include only those employees whose salary is between 62,000 and 70,000 (inclusive)
WHERE salary BETWEEN 62000 AND 70000;

Filtering date ranges with BETWEEN

The BETWEEN operator can also filter records within certain date ranges. This application is useful when analyzing data with date and time values. 

Assuming we have a column named hire_date in our employees table, we can use the BETWEEN operator to filter the dates and find records of employees hired within a specific period.

-- Select all columns from employees table
SELECT *
FROM employees
-- Filter records where hire date is between January 1, 2022, and December 31, 2022 (inclusive)
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

Combining BETWEEN with other operators

You can also combine the BETWEEN operator with other operators to achieve complex filtering scenarios. This technique is important if you want specific filtering. In the example below, we have used the equal (=) operator in the WHEN clause to filter rows containing only the Sales department. The query then uses the BETWEEN operator to filter the records further, including those salaries between 50,000 and 60,000.

-- Select all columns from employees table
SELECT *
FROM employees
-- Filter records where department is 'Sales' and salary is between 50,000 and 60,000 (inclusive)
WHERE department = 'Sales' AND salary BETWEEN 50000 AND 60000;

I recommend browsing around DataCamp’s collection of SQL courses to find a course that is right for you.

Limitations of the SQL Between Command

Finally, let's take a look at some limitations and alternatives. 

Limitations of the BETWEEN operator in SQL

The potential limitations of the SQL BETWEEN operator include:

• Potential Confusion of the Inclusive Boundaries: Since the BETWEEN operator includes the specified values as upper and lower bounds, it could lead to confusion or unexpected results if used incorrectly.
• Performance Issues with Large Datasets: Using the BETWEEN operator without specifying the indexes could lead to performance issues, especially when using large datasets.

Alternatives to the BETWEEN operator in SQL

Since the BETWEEN operator includes the specified values as lower and upper bounds, we can use the less than (<) and greater than (>) operators to achieve exclusive filtering. This technique will ensure these values are not included in the results.

-- Select the first name, last name, and salary columns from the Employees table
SELECT first_name, last_name, salary
FROM employees
-- Filter the results to include only those employees whose salary is greater than 50,000
-- and less than 60,000 (exclusive)
WHERE salary > 50000 AND salary < 60000;

Conclusion

The BETWEEN operator in SQL is useful for filtering values within given ranges. Understanding the importance of using the BETWEEN operator for data filtering and manipulation is important for data analysts. I encourage you to continue practicing the BETWEEN operator using different datasets for different SQL data types to improve your SQL skills.

Continuous learning will help you stand out among others in the professional field. I encourage you to take the more advanced Reporting in SQL course and to consider the comprehensive Associate Data Analyst in SQL career track. Finally, DataCamp also offers a SQL Associate Certification to help showcase your professional achievement in using SQL for data analysis.