DbSchema Database Designer

DbSchema Tutorial | SQL BETWEEN OPERATOR

Publish Article >>>

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!

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.


Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.


Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.


Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.


SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.


Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.


Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.


Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.


DbSchema can be downloaded for free. No registration is required.