BETWEEN
SQL

The SQL BETWEEN Clause Explained

intro

The SQL BETWEEN clause is the cloaked ghost of the database world: used infrequently, known little about, and when needed, turning us into the documentation. Hold on and we will explain what it is, why it’s famous for, and how to best work with it in the present day.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

What Is BETWEEN in SQL?

BETWEEN is an ANSI standards-compliant operator that selects values falling within a given range supported by relational database management systems. Contrary to a popular belief, this operator isn’t only used for mathematical operations. Instead, it’s used in many content management systems, search engines, and other technologies to help refine search queries and retrieve data.That makes the SQL BETWEEN operator a very frequent part of transactions. 

Here’s a basic example of how the SQL query looks like - we will run the query below:

Copy
        
1 SELECT username,email 2 FROM dbvisualizer_users 3 WHERE id BETWEEN 500 AND 1200;

The BETWEEN clause isn’t anything revolutionary and it’s basic use cases look like so:SELECT [columns] FROM [table] WHERE [column] [NOT] BETWEEN X and Y;
Where X and Y are values of any data type. The SQL query will return any rows that fall between the values X and Y and the values don’t necessarily have to be numeric.

Using BETWEEN in SQL

The SQL BETWEEN clause is frequently used in conjunction with:

  • Mathematical operators like <, >, <=, >=, and <=>
  • Joining or dividing operators (AND,OR)
  • The ORDER BY,ASC,DESC operators telling the database what order should the rows be going by.

These three operators are what makes the BETWEEN clause so incredibly powerful. The BETWEEN clause is a perfect companion for transactions because it can be used to update, select, or delete data that fits in between a certain “mold” and then do something other with it before finishing the operation. The reason the BETWEEN clause isn’t used very frequently is because there aren’t that many operations that warrant its use and its necessity to be combined with something makes it very limited.

SQL BETWEEN Clauses Inside Database Transactions

The most frequent use case of the SQL BETWEEN clause would be in transactions as part of other queries.s relational database management systems come with the ACID functionality that makes operations either complete all at once either not complete at all, the BETWEEN clause is a goldmine for some operations, but worthless for others. So, you really have to decide how are you going to use the BETWEEN clause to help your project.

BETWEEN Dates and Text Values

If you want BETWEEN to be a part of your next project, keep in mind that the clause doesn’t only accept numeric values – values can also be based upon text or dates. To match text, simply encluse the text inside of quotes like so:

SELECT X WHERE Y BETWEEN ‘A’ and ‘Z’;

The BETWEEN clause can also be used to do other things – one can combine the clause with RIGHT, LEFT, or SUBSTRING_INDEX functions for fuzzy matching.

Browser-based SQLite viewer example

The bottom line is simple – BETWEEN is to be used the same way regardless if we’re matching dates, text, or integers. SQL BETWEEN dates operations are particularly common, too: for many DBAs, finding something in between two dates is indeed invaluable.

Real-World Use Cases & Lifehacks

The SQL BETWEEN clause isn’t a very frequent friend in the daily work of an ordinary DBA - that’s not to say that it doesn’t have some exotic use cases when it’s needed though. Here are some:

  1. One can use “#” in place of “‘“ or ‘“‘ - SELECT * FROM users WHERE reg_date BETWEEN #12/01/2017# AND #12/31/2018#;
  2. BETWEEN can be switched to mathematical operators - SELECT id, name FROM events WHERE eventdate >= '07/07/2023' AND eventdate < '07/27/2023';
  3. BETWEEN is frequently used with IN to select IDs falling within a given range  - SELECT * FROM events WHERE price BETWEEN 5 AND 10 AND eventid IN (7,8,9);
  4. BETWEEN can be used with wildcards - SELECT * FROM users WHERE username BETWEEN 'a%' AND 'd%'; would return all users whose username starts with a, b or c (excluding d.)
  5. NOT BETWEEN is an option too - NOT BETWEEN is just what it sounds like - it’s an opposite to BETWEEN.

Also, we’d like you to keep in mind that some of the most frequent use cases involving BETWEEN also involves the usage of dates: in other words, if your data set has a lot of dates, BETWEEN can be very beneficial.

To reap all of the benefits of BETWEEN and other SQL clauses, though, consider using SQL clients to assist you - they will not only help you out in day-to-day tasks, but off-load some of the other burden from your shoulders too.

SQL Clients in Between

When you find yourself using the SQL BETWEEN clause, you’re entering a territory frequented by experienced DBAs. That’s not to say that BETWEEN should only be used by Seniors, but rather that you should familiarize yourself with the internals of the database management system you find yourself using.

When you start familiarizing yourself with the internals, you should inevitably come across SQL clients.

SQL clients like DbVisualizer can help you to easily view and visualize your data, build and visualize SQL queries on it, explore it in a visual manner, export it as a spreadsheet, optimize your databases, and much more. No matter if you’re using ClickHouse, Oracle, MongoDB, or MySQL (perhaps you find yourself using Cassandra?) – DbVisualizer supports all of them. And with the tool being used by the world’s leading tech companies including Google, Meta, Netflix, Tesla, and even NASA, you can’t go wrong. Give DbVisualizer a try today and see how it can transform your database operations!

FAQ

When Should I Use the SQL BETWEEN Clause?

The SQL BETWEEN clause should be used whenever you need to find values that fall in between two given ranges - in other words, use this clause to test whether a given expression falls within a range of values.

How to Use the SQL BETWEEN Clause?

Include the SQL BETWEEN clause after specifying the WHERE clause like so (note that the NOT BETWEEN clause is perfectly OK too): SELECT x,y FROM table_name WHERE z [NOT] BETWEEN 200 AND 400;

What Data Types Can I Use BETWEEN With?

BETWEEN can be used with all data types, including text-based data types, numeric data types, and data-based data types. Here’s an example of a text-based data type being in use:

Copy
        
1 SELECT * FROM table_name 2 WHERE product_name BETWEEN ‘Chinese Seasoning’ AND ‘Korean Seasoning’ 3 ORDER BY product_name;

Such an SQL query would select all products that fall in between Chinese seasoning and Korean seasoning.

BETWEEN can be used with all data types, including text-based data types, numeric data types, and data-based data types. Here’s an example of a text-based data type being in use:

Such an SQL query would select all products that fall in between Chinese seasoning and Korean seasoning.

SQL clients should be used because they help alleviate the burden caused by the complexities of whatever DBMS you may find yourself using. SQL clients like the one provided by DbVisualizer come with various tools and features to help make your databases sing, no matter what tech they’re based on.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

MySQL Backup and Recovery Best Practices: A Guide

author Lukas Vileikis tags BIG DATA MySQL OPTIMIZATION SECURITY SQL 7 min 2024-10-15
title

SQL Not Equal Operator: Definitive Guide with Examples

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-10-14
title

MySQL DISTINCT Clause Explained: Why, How & When

author Lukas Vileikis tags BEGINNER MySQL SQL 7 min 2024-10-10
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

Distributing Data in a Database: A Guide to Database Sharding

author Lukas Vileikis tags MySQL OPTIMIZATION SQL 7 min 2024-09-09
title

MariaDB Docker: Server Setup Guide

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-29
title

MySQL Error #1045 Explained: Everything You Need to Know

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-26

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗