BETWEEN in SQL

📏 The BETWEEN operator in SQL is used to filter records whose values fall within a specified range. It can be used with numeric values, text, and dates. The range is inclusive, meaning both the starting and ending values are included in the result.

📖 What is the BETWEEN Operator?

The BETWEEN operator simplifies range-based filtering. Instead of writing multiple comparison operators such as >= and <=, you can use a single, more readable expression.

Information

BETWEEN is equivalent to using both >= (greater than or equal to) and <= (less than or equal to).

📝 Basic Syntax

General Syntax

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

📊 Sample Table

Consider the following Students table:

StudentIDNameDepartmentMarksAdmissionDate
101AliceComputer Science922024-01-15
102BobMathematics852024-02-10
103CharliePhysics782024-03-20
104DavidComputer Science952024-04-05
105EvaMathematics882024-05-12

đŸ”ĸ BETWEEN with Numeric Values

Retrieve students whose marks are between 80 and 90.

Numeric BETWEEN Example

SELECT *
FROM Students
WHERE Marks BETWEEN 80 AND 90;

This query returns students whose marks are greater than or equal to 80 and less than or equal to 90.

📅 BETWEEN with Dates

The BETWEEN operator is commonly used to filter records within a date range.

Date Range Example

SELECT *
FROM Students
WHERE AdmissionDate
BETWEEN '2024-02-01' AND '2024-04-30';

This query returns students admitted between February 1, 2024, and April 30, 2024, including both dates.

🔤 BETWEEN with Text

BETWEEN can also compare text values. The results depend on the database's collation and sorting rules.

Text Range Example

SELECT *
FROM Students
WHERE Name BETWEEN 'A' AND 'D';

Important

Text comparisons use the database's collation rules. Different collations may produce different ordering and comparison results.

đŸšĢ Using NOT BETWEEN

The NOT BETWEEN operator returns rows whose values fall outside the specified range.

NOT BETWEEN Example

SELECT *
FROM Students
WHERE Marks NOT BETWEEN 80 AND 90;

This query returns students whose marks are less than 80 or greater than 90.

🔗 BETWEEN with AND

Combine BETWEEN with additional conditions using the AND operator.

BETWEEN with AND

SELECT *
FROM Students
WHERE Marks BETWEEN 80 AND 95
AND Department = 'Computer Science';

🔀 BETWEEN with OR

You can also combine BETWEEN with the OR operator.

BETWEEN with OR

SELECT *
FROM Students
WHERE Marks BETWEEN 90 AND 100
OR Department = 'Physics';

📈 BETWEEN with ORDER BY

After filtering records with BETWEEN, you can sort the results using ORDER BY.

BETWEEN with ORDER BY

SELECT Name,
       Marks
FROM Students
WHERE Marks BETWEEN 80 AND 95
ORDER BY Marks DESC;

⚖ī¸ BETWEEN vs Comparison Operators

Using BETWEENEquivalent Comparison
Marks BETWEEN 80 AND 90 Marks >= 80 AND Marks <= 90
Date BETWEEN '2024-01-01' AND '2024-12-31' Date >= '2024-01-01' AND Date <= '2024-12-31'

📊 Common Uses of BETWEEN

Data TypeExample
Numbers Price BETWEEN 100 AND 500
Dates OrderDate BETWEEN '2025-01-01' AND '2025-12-31'
Text Name BETWEEN 'A' AND 'M'

đŸ’ŧ Real-World Example

A university administrator wants to generate a report showing students who scored between 85 and 95, sorted from the highest marks to the lowest.

Real-World Query

SELECT StudentID,
       Name,
       Department,
       Marks
FROM Students
WHERE Marks BETWEEN 85 AND 95
ORDER BY Marks DESC;

This query retrieves only students whose marks fall within the specified range and displays them in descending order.

⚠ī¸ Best Practices

Best Practice

Use BETWEEN for inclusive range checks to improve readability. Ensure the lower value appears first and the higher value second. For date ranges involving date-time values, be mindful of the time component, as it may affect which rows are included.

🚀 Key Points to Remember

  • 📌 BETWEEN filters values within an inclusive range.
  • 📌 It works with numbers, dates, and text values.
  • 📌 NOT BETWEEN returns values outside the specified range.
  • 📌 BETWEEN is equivalent to using >= and <=.
  • 📌 It can be combined with AND, OR, and ORDER BY.
  • 📌 Date and text comparisons depend on the stored values and database collation rules.
>>"The BETWEEN operator makes range-based filtering simple, readable, and efficient."

Summary

✅ The BETWEEN operator is a convenient SQL feature for filtering values within an inclusive range. Whether working with numbers, dates, or text, it provides a cleaner alternative to multiple comparison operators and helps create clear, maintainable, and efficient SQL queries.