The SQL BETWEEN Operator

SQL comparison operators are essential tools for filtering data in database queries. Among these operators, the BETWEEN operator stands out as an important tool for specifying ranges inside datasets.

SQL comparison operators, such as BETWEEN, are useful for filtering data in database queries. The BETWEEN operator allows you to select rows within a given range, making it ideal for tasks like segmenting an audience by age ranges, identifying thresholds for items between certain quantities, or categorizing performance based on exam scores.

In this article, we'll go over the fundamentals of the SQL BETWEEN operator. We’ll focus on practical examples and realistic advice for getting the most out of it. Whether you're an SQL beginner or want to improve your skills, understanding the SQL BETWEEN operator will improve your SQL queries.

If you are new to SQL, why not check out our SQL Basics course? If you’re looking to review and consolidate your SQL skills, our SQL Practice track will take you further along your learning journey.

With that being said, let's get started on discovering the power of SQL BETWEEN!

Understanding the SQL BETWEEN Operator

BETWEEN Basic Syntax

The SQL BETWEEN operator follows a simple syntax. Finding it hard to remember SQL syntax and operator names? Don’t worry; we’ve got you covered with our SQL Cheat Sheet!

The typical structure for BETWEEN is as follows:

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

Let’s break down the syntax:

• SELECT: Specifies which columns to retrieve data from.
• FROM: The table that stores the data.
• WHERE: Filters the rows based on specific criteria.
• BETWEEN: The BETWEEN operator itself.
• value1: The lower limit of the range.
• value2: The upper limit of the range.

A Practical Example of BETWEEN

It is important to be aware that the BETWEEN operator includes both the lower and upper boundaries. In other words, it includes values equal to value1, value2, and any value that falls between them in the result set. Let’s illustrate with an example.

Imagine we are working in a department store and we want to retrieve all information on our products where the price is between $12.99 and $19.99 inclusive:

SELECT *
FROM products
WHERE price BETWEEN 12.99 AND 19.99;

This query will return all rows from the products table whose price is equal to 12.99, 19.99, or equal to any price between 12.99 and 19.99. As seen here, using the BETWEEN operator here simplifies and clarifies the code, making it easier to read and maintain. For more practical beginner examples, check out our 10 Beginner SQL Practice Exercises with Solutions.

Simplifying Queries with BETWEEN

You may have already noticed that the BETWEEN operator is essentially a shortcut for combining the >= and <= operators. Using the syntax column_name BETWEEN value1 AND value2 is equivalent to stating column_name >= value1 AND column_name <= value2. Applying this syntax to our prior query, the end results will be the same:

SELECT *
FROM products
WHERE price >= 50 AND price <= 100;

However, the BETWEEN operator provides a more compact and obvious syntax than using the >= and <= operators. It simplifies query creation, reduces mistakes, and improves readability – making it the recommended method for specifying range conditions in SQL queries.

The NOT BETWEEN Operator

In SQL, we often use the logical operator NOT to remove rows from our data that do not meet the given condition. Similarly, we can use NOT BETWEEN to achieve the opposite results of the BETWEEN operator.

In other words, using the BETWEEN operator filters for results inside the specified range. Using the NOT BETWEEN operator filters for results outside the range.

Consider a scenario where we want to extract all data from the customers table where the customers' ages are not between 25 and 30. We can accomplish this with the NOT BETWEEN operator:

SELECT *
FROM customers
WHERE age NOT BETWEEN 25 AND 30;

In this query:

• SELECT *: Specifies that we wish to retrieve all columns.
• FROM customers: The table from which to retrieve data.
• WHERE AGE IS NOT BETWEEN 25 AND 30: Selects rows where the age column value is less than 25 and over 30.

Other examples of this method could be locating products with quantities outside of a specified range, finding employees employed outside of a given time period, or identifying consumers with total purchases below or above a certain amount.

Using the BETWEEN Operator with Text Values

While often associated with numerical ranges, the SQL BETWEEN operator can also be applied to text values.

Imagine we are back in the department store scenario with the table products. Using the column category (which contains product categories as text values), we want to retrieve all the categories that fall alphabetically between specific categories.

SELECT *
FROM products
WHERE category BETWEEN 'Electronics' AND 'Toys and Games';

In this query:

• SELECT *: Indicates that we wish to retrieve all columns.
• FROM products: The table from which the data will be retrieved.
• WHERE category IS BETWEEN 'Electronics' AND 'Toys and Games': Filters the rows to include only those whose category column value falls between 'Electronics' and 'Toys and Games' in alphabetical order, inclusively.

As you can see, using the SQL BETWEEN operator with text values allows you to easily filter data based on alphabetical order. Another example could include retrieving information on customers whose surnames fall within a specific alphabetical range:

SELECT *
FROM customers
WHERE last_name BETWEEN 'B' AND 'G';

Similarly, within the department store scenario, you could filter columns that follow a sequence (e.g. product codes):

SELECT *
FROM products
WHERE product_code BETWEEN 'C100' AND 'C150';

These examples not only demonstrate the practicality of the BETWEEN operator but also its versatility in filtering more than just numeric data.

Using the SQL BETWEEN Operator with Dates

The BETWEEN operator in SQL can also be useful for selecting data within specific date periods.

Let’s return to our department store scenario and imagine we have a table named orders with a column called order_date that contains the dates when orders were placed. Let's examine how we may use this way to retrieve all orders placed between in the first week of April 2024:

SELECT * FROM sales
WHERE sale_date BETWEEN '2024-04-01' AND '2024-04-07';

In this query:

• SELECT *: Indicates that we want to retrieve all columns.
• FROM sales: Gives the table from which the data will be retrieved.
• WHERE sales_date BETWEEN '2024-04-01' AND '2024-04-07': Filters the rows to those whose order_date falls between April 1st, 2024 and April 7th, 2024 inclusive.

Important: When using the BETWEEN operator with date or timestamp data, it includes both the start and finish dates but excludes the time component. Writing '2024-04-07' is the equivalent to '2024-04-07 00:00:00'. The results from this query will include rows from '2024-04-01 00:00:00' (i.e. 12 AM April 1) to '2024-04-07 00:00:00', meaning that sales after midnight on the 7th will be excluded. To overcome this limitation, you could extend your query like this:

SELECT * FROM sales 
WHERE sale_date >= '2024-04-01' AND sale_date <= '2024-04-07';

To avoid mistakes, it is better to use the <= (less than or equal to) and >= (greater than or equal to) operators, especially if you're unsure about the data type. By using this method you are including the complete date range, including any timestamps within the specified dates.

Time to Practice SQL BETWEEN

To conclude, the SQL BETWEEN operator is a must have tool in your SQL toolkit. It can be used on a variety of data types, including numbers, text, and dates. Its straightforward syntax and inclusive nature make it an ideal choice for filtering rows based on  specified criteria.

However, it is important to be aware of BETWEEN’s behavior – especially when dealing with date and timestamp values – to ensure the reliability of your results. Although the BETWEEN operator expresses range conditions concisely, it is better to use the <= AND >= syntax to include a complete date range.

By understanding the SQL BETWEEN syntax, uses, and behaviors, you can improve query performance and data retrieval accuracy. The BETWEEN operator is a must-have tool for SQL experts, allowing for optimized database processes and efficient data extraction. Ready to go deeper? Try our SQL Practice course and start creating your very own BETWEEN queries!