BETWEEN Operator in SQL: Syntax & Examples with FAQs
In SQL, BETWEEN is a logical operator. BETWEEN operator is used in SQL to filter the result set within a certain range. BETWEEN operator allows you to specify a range of values in a WHERE clause to filter result set. Understanding the BETWEEN operator enhances the ability to perform complex query, retrieve targeted data, and extract valuable insights from datasets. This article provides a detailed overview of the BETWEEN operator with example. After reading this article you can use the BETWEEN operator easily and retrieve valuable data from a table.
The range is inclusive, which means that the boundary values are part of the result. BETWEEN operator can be applied to numbers, text and dates.
Syntax
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Demo Database
CREATE TABLE `Users` ( `Id` int NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Mobile` varchar(10) DEFAULT NULL, `DOB` date DEFAULT NULL, `Weight` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`Id`) );
Id Name Mobile DOB Weight ----------------------------------------------------------------- 1 Deepak 9999999991 1993-01-15 60.00 2 Smith 9999999999 1994-11-20 85.00 3 Naveen 9999999992 1997-01-15 55.00 4 Deepak Sharma 9999999993 1998-08-03 null 5 Jane Smith 9999999995 1993-10-05 73.00 6 Jone Head 9999999991 1983-01-15 90.00 7 Deepak Kumar 9999999999 1984-11-20 85.00 8 Jack 9999999992 1987-01-15 59.00 9 Ramesh 9999999993 1988-08-03 86.00 10 David 9999999995 1983-10-05 70.00
BETWEEN Operator With Number
BETWEEN Operator is typically useful when you want to filter the result set within a certain range. The range is inclusive that means begin and end values are included.
SQL query to retrieve Users whose Weight is between 60 and 85 (60 and 85 included in result set).
SELECT Id, Name, Mobile, DOB, Weight FROM Users WHERE Weight BETWEEN 60 and 85;
Id Name Mobile DOB Weight ---------------------------------------------------------------- 1 Deepak 9999999991 1993-01-15 60.00 2 Smith 9999999999 1994-11-20 85.00 5 Jane Smith 9999999995 1993-10-05 73.00 7 Deepak Kumar 9999999999 1984-11-20 85.00 10 David 9999999995 1983-10-05 70.00
BETWEEN Operator With Date
SQL query to retrieve users whose Date of Birth is between 1980-01-01 and 1988-08-03.
SELECT Id, Name, Mobile, DOB, Weight FROM Users WHERE DOB BETWEEN "1980-01-01" and "1988-08-03";
Id Name Mobile DOB Weight ---------------------------------------------------------------- 6 Jone Head 9999999991 1983-01-15 90.00 7 Deepak Kumar 9999999999 1984-11-20 85.00 8 Jack 9999999992 1987-01-15 59.00 9 Ramesh 9999999993 1988-08-03 86.00 10 David 9999999995 1983-10-05 70.00
BETWEEN Operator With Text
SQL query to retrieve users whose names are between 'deepak' and 'jack'.
SELECT Id, Name, Mobile, DOB, Weight FROM Users WHERE Name BETWEEN "deepak" and "jack";
Id Name Mobile DOB Weight 1 Deepak 9999999991 1993-01-15 60.00 4 Deepak Sharma 9999999993 1998-08-03 null 7 Deepak Kumar 9999999999 1984-11-20 85.00 8 Jack 9999999992 1987-01-15 59.00
Indexing With BETWEEN
Indexes are fundamental to database optimization, enabling faster retrieval of records. Properly maintained and well-designed indexes are essential for achieving optimal database performance. When used with the BETWEEN operator, indexes can significantly enhance query performance by narrowing down the search range.
How Indexes Work with BETWEENWhen you create an index on a column that used in a BETWEEN query, the database engine can efficiently traverse the index to locate the start and end points of the range. This is much faster than performing a full table scan, especially for large tables.
Key Characteristics of BETWEEN Operator
- The BETWEEN operator is a powerful and user-friendly tool for filtering result sets within a specified range.
- The BETWEEN works with numeric, date and text data types.
- The BETWEEN operator returns True if the value of specified column is greater than or equal to value1 and less than or equal to value2.
- It can be used with SELECT, UPDATE, DELETE and INSERT SQL statements.
- Using BETWEEN enhances the readability of queries compared to using >= and <=.
- Indexes enable the database engine to quickly locate the range of values specified in the BETWEEN clause, speeding up query execution.
Frequently Asked Questions (FAQs)
1. What does the BETWEEN operator do in SQL?
The BETWEEN operator filters rows where a column’s value falls within a specified range, including both boundary values. It is commonly used with numbers, dates and text. BETWEEN improves readability compared to writing >= and <= conditions separately, while still returning precise and predictable results when used correctly.
2. Is the BETWEEN range inclusive or exclusive?
BETWEEN is inclusive on both ends. That means values equal to the lower boundary and the upper boundary are included in the result set. For example, Weight BETWEEN 60 AND 85 returns records where Weight is 60, 85 or any value in between. This behavior is consistent across major SQL databases.
3. Can BETWEEN be used with date values?
Yes. BETWEEN works very well with dates and is commonly used for date filtering. However, you must be careful with time components. For DATETIME columns, the end value may exclude records unless the full timestamp is specified. Using date ranges with clear boundaries ensures accurate and predictable results.
4. How does BETWEEN behave with NULL values?
If the column value is NULL, BETWEEN will never return TRUE. Comparisons involving NULL result in UNKNOWN, which means those rows are excluded from the result set. If NULL values matter for your logic, explicitly handle them using IS NULL or COALESCE() alongside BETWEEN.
5. Can BETWEEN be used with text (VARCHAR) columns?
Yes. BETWEEN works with text values using lexicographical ordering based on collation rules. Results depend on case sensitivity and collation settings of the database. Always ensure consistent casing or use functions like LOWER() when working with string ranges to avoid unexpected results.
6. What happens if the start value is greater than the end value?
If the lower boundary is greater than the upper boundary, BETWEEN returns no rows. SQL does not automatically swap values. For example, Weight BETWEEN 85 AND 60 will always return an empty result. Always ensure correct ordering or validate inputs before querying.
7. Is BETWEEN faster than using >= and <= ?
BETWEEN and >= AND <= are logically equivalent and generally have the same performance. Query optimizers treat them similarly. BETWEEN is preferred mainly for readability and clarity, not speed. Performance depends more on indexing and data distribution.
8. Can BETWEEN be used with UPDATE and DELETE statements?
Yes. BETWEEN works with UPDATE and DELETE exactly the same way it works with SELECT. However, since these operations modify data, extra caution is required. Always test the condition with a SELECT query first and ensure backups exist before executing destructive operations.
9. How does BETWEEN interact with indexes?
BETWEEN is index-friendly when used on indexed columns. The database can efficiently scan the index between the specified start and end values, making queries much faster than full table scans. This is especially beneficial for large datasets.
10. Can BETWEEN be combined with other operators like AND or OR?
Yes. BETWEEN is often combined with AND, OR and NOT to create more refined filters. Parentheses should always be used when combining logical operators to ensure correct evaluation order. Clear grouping prevents logical bugs and improves maintainability.
11. How is BETWEEN different from IN?
BETWEEN checks for values within a continuous range, while IN checks for specific discrete values. BETWEEN is ideal for numeric and date ranges, whereas IN is better for predefined sets like IDs or categories. Choosing the right operator improves both clarity and correctness.
12. Can BETWEEN be used with floating-point numbers?
Yes, but floating-point comparisons can be tricky due to precision issues. BETWEEN works correctly, but values stored as FLOAT or DOUBLE may not behave as expected at boundaries. For financial or precise numeric ranges, DECIMAL is recommended.
13. Does BETWEEN respect case sensitivity in text comparisons?
Case sensitivity depends on database collation. MySQL is often case-insensitive by default, while PostgreSQL is case-sensitive unless configured otherwise. Always confirm collation settings or normalize text using LOWER() or UPPER() when consistent behavior is required.
14. When should BETWEEN be avoided?
BETWEEN should be avoided when range boundaries are ambiguous, when NULL handling is critical or when inclusive logic causes edge-case bugs, especially with DATETIME columns. In such cases, explicit >= and < conditions provide clearer control.
15. Is BETWEEN commonly asked in SQL interviews?
Yes. BETWEEN is frequently tested in SQL interviews, especially with date ranges, indexing behavior and boundary conditions. Interviewers often check whether candidates understand inclusivity, NULL behavior and correct usage with DATETIME columns.