Mastering SQL BETWEEN Operator: Filtering Data within a Range

How Does the BETWEEN Operator Work in SQL?

The BETWEEN operator in SQL is used to filter rows within a specific range of values. It is particularly useful for querying data that falls between two values, such as numbers, dates, or text (depending on collation settings).

---

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• The range specified in the BETWEEN clause includes the lower bound (value1) and upper bound (value2).
• The range is inclusive.

---

How It Works

1. Numeric Ranges:
   
   Used to filter rows where a column’s numeric value falls between two specified numbers.
   
   Example: Finding prices between 10 and 50.

2. Date Ranges:
   
   Helps fetch records within a specific date range.
   
   Example: Retrieving orders placed between January 1 and January 31.

3. Text Ranges (depends on collation):
   
   Can be used to select rows where text values fall alphabetically within a range.

---

Examples

1. Numeric Range

Consider a table Products:

ProductID	ProductName	Price
1	Laptop	1200
2	Mouse	25
3	Keyboard	45
4	Monitor	200

Query:

SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 30 AND 300;

Result:

ProductName	Price
Keyboard	45
Monitor	200

---

2. Date Range

Consider a table Orders:

OrderID	OrderDate	CustomerID
101	2023-01-10	1
102	2023-01-15	2
103	2023-02-05	3

Query:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

Result:

OrderID	OrderDate
101	2023-01-10
102	2023-01-15

---

3. Text Range

Consider a table Students:

StudentID	Name
1	Alice
2	Bob
3	Charlie

Query:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

---

Result:

Name

Alice

Bob

Charlie

---

Key Points

1. Inclusivity:

   • Both value1 and value2 are included in the range. Example: BETWEEN 10 AND 20 includes 10 and 20.

2. Range Order:

   • The lower value must come first (value1) and the higher value second (value2), otherwise the query will not return correct results.

3. NOT BETWEEN:

   • To exclude a range, use the NOT keyword. Example:

SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 30 AND 300;

1. Performance:
   • When working with large datasets, make sure indexes are applied to the column being queried to enhance performance.

---

Conclusion

The BETWEEN operator simplifies querying ranges in SQL, making it an indispensable tool for filtering data based on numeric, date, or even text ranges. Its inclusivity and ease of use make it one of the most common SQL operators in everyday database management.

Hi, I'm Abhay Singh Kathayat!
I am a full-stack developer with expertise in both front-end and back-end technologies. I work with a variety of programming languages and frameworks to build efficient, scalable, and user-friendly applications.
Feel free to reach out to me at my business email: kaashshorts28@gmail.com.

The above is the detailed content of Mastering SQL BETWEEN Operator: Filtering Data within a Range. For more information, please follow other related articles on the PHP Chinese website!