DbSchema Tutorial | SQL BETWEEN OPERATOR

Publish on DbSchema Blog >>>

SQL, the language of databases, boasts a plethora of operators designed to facilitate complex queries. Among these, the BETWEEN operator holds significance for its range-based filtering. This article will provide a thorough understanding of the BETWEEN operator, accompanied by detailed examples.

1. Introduction to the SQL BETWEEN OPERATOR

At its core, the BETWEEN operator is a range filter, designed to select values within a specified interval.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Sample Data: Students Table

ID	Name	Age
1	Alice	20
2	Bob	22
3	Carol	25
4	Dave	19
5	Eve	27

Query:

Identify students aged between 20 and 25.

SELECT Name, Age
FROM students
WHERE Age BETWEEN 20 AND 25;

Result:

Name	Age
Alice	20
Bob	22
Carol	25

Explanation:

The query lists students aged 20 to 25. It’s crucial to note that BETWEEN is inclusive, meaning it includes the boundary values.

2. LIKE vs. BETWEEN OPERATOR: A Detailed Comparison

While both LIKE and BETWEEN filter data, their applications differ significantly. LIKE is pattern-centric, while BETWEEN is range-centric.

Feature	LIKE Operator	BETWEEN Operator
Purpose	Filters based on patterns	Filters based on a value range
Typical Usage	Strings	Numbers, Dates, Strings
Syntax Example	WHERE name LIKE 'A%' (Selects names starting with A)	WHERE age BETWEEN 20 AND 25 (Selects age range)
Flexibility	Utilizes wildcards like % and _ for versatile pattern matches	Strictly adheres to the defined value range

3. Negating Ranges with NOT

Sometimes, the requirement is to filter out values within a certain range. This is where NOT comes in.

Query:

Identify students NOT aged between 20 and 25.

SELECT Name, Age
FROM students
WHERE Age NOT BETWEEN 20 AND 25;

Result:

Name	Age
Dave	19
Eve	27

Explanation:

The query omits students aged between 20 and 25, returning only those outside this age bracket.

4. Diverse Applications of BETWEEN: Numbers, Dates, Texts, and Times

a) Numeric Values:

Using the students table, we can filter students based on age ranges, as shown in earlier examples.

b) Date Values:

Sample Data: Sales Table

SaleID	Product	Date
101	Laptop	2023-01-15
102	Phone	2023-03-20

Query:

Fetch sales from January to March 2023.

SELECT *
FROM sales
WHERE date BETWEEN '2023-01-01' AND '2023-03-31';

Result:

SaleID	Product	Date
101	Laptop	2023-01-15
102	Phone	2023-03-20

Explanation:

This query returns all sales within the first three months of 2023.

c) Text:

Using our students table:

Query:

Fetch students with names alphabetically between Alice and Dave.

SELECT Name
FROM students
WHERE Name BETWEEN 'Alice' AND 'Dave';

Result:

Name
Alice
Bob
Carol
Dave

Explanation:

This query lists students whose names fall alphabetically between Alice and Dave, inclusive.

d) Times:

Assuming a logs table with time-based entries:

LogID	Event	Time
1	Login	08:30:00
2	File Access	09:45:00

Query:

List events between 08:00 and 10:00.

SELECT *
FROM logs
WHERE time BETWEEN '08:00:00' AND '10:00:00';

Result:

LogID	Event	Time
1	Login	08:30:00
2	File Access	09:45:00

Explanation:

This returns logs of events that occurred from 08:00 to 10:00.

5. Synergizing BETWEEN with Other SQL Operators

BETWEEN can be seamlessly integrated with operators like AND, OR, and IN.

Query:

Fetch students aged between 20-25 OR named Eve.

SELECT Name, Age
FROM students
WHERE Age BETWEEN 20 AND 25 OR Name = 'Eve';

Result:

Name	Age
Alice	20
Bob	22
Carol	25
Eve	27

Explanation:

This query combines the BETWEEN operator with the OR operator to fetch the desired results.

6. BETWEEN’s Role in Update and Delete Statements

a) UPDATE:

Using our students table:

Query:

Add a year to students aged between 20 and 22.

UPDATE students
SET Age = Age + 1
WHERE Age BETWEEN 20 AND 22;

Updated Students Table:

ID	Name	Age
1	Alice	21
2	Bob	23
3	Carol	25
4	Dave	19
5	Eve	27

Explanation:

The ages of Alice and Bob increase by one year.

b) DELETE:

Query:

Remove records of students aged 23-25.

DELETE FROM students
WHERE Age BETWEEN 23 AND 25;

Updated Students Table:

ID	Name	Age
1	Alice	21
4	Dave	19
5	Eve	27

Explanation:

Bob and Carol’s records, falling within the specified age range, get deleted.

7. Common Mistakes and How to Avoid Them

• Boundary Confusion: Always remember that BETWEEN is inclusive of the boundary values.
• Data Type Mismatch: Ensure the data type of the values matches the column’s data type.
• Overlapping Ranges: When using multiple BETWEEN conditions, ensure ranges don’t overlap unless intended.

8. FAQs

• Is BETWEEN inclusive of boundary values? Yes, both boundary values are included.
• Can BETWEEN be used with strings? Absolutely! It works based on alphabetical order.

9. Practice Corner: Test Your Knowledge

1. In a products table, fetch items priced between $15 and $60.
2. From the orders table, list orders placed in the first half of 2023.
3. Using an employees table, identify staff with names ranging from Anna to Kyle.
4. In a log table, extract logs from midday to 4 pm.

Conclusion

The SQL BETWEEN operator is a powerful tool, enabling precise range-based data querying. As we’ve delved into its various applications and intricacies, it’s clear that mastering such functionalities is essential for effective database management. Through practice and understanding, users can harness its full potential to streamline data retrieval and manipulation tasks.

Happy querying!