How to read values within a defined spectrum using SQL BETWEEN

SQL BETWEEN is used to specify a range of values for a new output. The operator works with numbers, data and texts.

What is SQL BETWEEN?

To use the Structured Query Language to output entries within a database that are within a defined range of values, the SQL BETWEEN operator is useful. This can not only be applied to numerical values, but also works with data or text entries. The operator works inclusively, so that the first and last entry used to limit the spectrum are included in the enumeration. SQL BETWEEN is built into a WHERE condition and works with the SQL commands DELETE, INSERT, SELECT and UPDATE.

Syntax and function

Let’s take a closer look at the interaction of SQL BETWEEN with the SELECT command and the WHERE condition:

SELECT column1, column2, ... 
FROM name_of_table 
WHERE name_of_column BETWEEN initial_value AND final_value;

Use column1, column2 etc. to specify which columns are to be displayed. Then enter the name of the table in which these columns are to be found. With name_of_column you specify the values that are to be taken into account and then use BETWEEN initial_value AND final_value to specify the range in which the values are to be located.

Example for using the operator

To illustrate how SQL BETWEEN works, we’ll create a sample table called “Customer list”. In this table, we list the customer number, name, location, order date, number of items ordered and the total amount in dollars that these customers have paid.

To filter this list, we can use the operator. For example, it’s possible to display only those customers who have invested between 300 and 700 dollars in the estimated period. This is the corresponding code:

SELECT customer number, name, location, date, product, sum
FROM Customer list 
WHERE Sum BETWEEN 300 AND 700;

The corresponding output is:

Words as a spectrum of values

SQL BETWEEN also works with words. The first letters of the stored text values are crucial. In the example below, we only consider entries alphabetically between “Muller” and “Stuart”. Here’s the code:

SELECT customer number, name, location, date, product, sum
FROM Customer list
WHERE Name BETWEEN 'Muller' AND 'Stuart'
ORDER BY Name;

The output is this new table:

The entries “Muller” and “Stuart” are included since the operator is inclusive. Using the ORDER BY Name statement, we ensure the output is in alphabetical order.

Select by DATE

To view order transactions processed within a specific time frame, you can use SQL BETWEEN. In the example below, we filter our customer list for orders between January 10 and January 31, 2024. Here’s the code:

SELECT customer number, name, location, date, product, sum
FROM Customer list
WHERE Date BETWEEN '10/1/2024' AND '31/1/2024';

The result is this table:

Operators similar to SQL BETWEEN

You can also display results that are outside the defined spectrum. To do this, use NOT BETWEEN. The approach is very similar. In our example, if we want to display the customers who spent less than 300 or more than 700 dollars, it works as follows:

SELECT customer number, name, location, date, product, sum 
FROM Customer list
WHERE Sum NOT BETWEEN 300 AND 700;

The output only contains two entries: