Online College Courses for Credit

+
BETWEEN to Filter Data

BETWEEN to Filter Data

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a SELECT statement that uses BETWEEN to search for a range of numerical values in a data set.

(more)
See More
Tutorial

what's covered
This tutorial explores using the BETWEEN operator within a SELECT statement to filter data based on ranges in three parts:

  1. Getting Started
  2. Using BETWEEN on Dates
  3. Adding NOT

1. Getting Started

The BETWEEN is an operator that allows us to check if an attribute in within a range of values. It would be similar to using two separate conditions using the logical operators but helps to simplify this further. The values defined in the BETWEEN operator are inclusive of what is searched for.

It is important to note that we always need to specify the smaller value first. For example, if we have the following statement:

SELECT *
FROM customer
WHERE support_rep_id BETWEEN 1 AND 4;

This should return a result set with 41 rows.

File:11235-1190-1.png

This command can be interpreted as follows:

SELECT *
FROM customer
WHERE support_rep_id >= 1
AND support_rep_id <= 4;

This command would also return 41 rows.


File:11236-1190-2.png

However, if we have the larger number first:

SELECT *
FROM customer
WHERE support_rep_id BETWEEN 4 AND 1;

It would try to run it as the following:

SELECT *
FROM customer
WHERE support_rep_id >= 4
AND support_rep_id <= 1;

Of course, this would not work. The support_rep_id could not be greater than or equal to 4 at the same time as the support_rep_id is less than or equal to 1. It could only exist in one of those ranges but not both. Therefore, no rows could match that criteria, which is why we would get the following result in either case.

File:11237-1190-3.png



2. Using BETWEEN on Dates

You can also use BETWEEN for dates when comparing to a range of dates. For example, if we wanted to search for invoices that had the invoice_date in the month of February in 2009, we could do the following:

SELECT *
FROM invoice
WHERE invoice_date BETWEEN '2009-03-01' AND '2009-03-31';

File:11238-1190-4.png


This would be simpler than having two separate parts:

SELECT *
FROM invoice
WHERE invoice_date >= '2009-03-01'
AND invoice_date <= '2009-3-31';


3. Adding NOT

You can also use the NOT operator that returns the opposite result set. Using an example from the prior tutorial, if we wanted to get the tracks that had the genre_id NOT being between 10-20 in case there were additional genre_ids added beyond 20, we could write our query like this:

SELECT *
FROM track
WHERE genre_id NOT BETWEEN 10 AND 20;

This would include all of the tracks between 1-9 and 21-25 (along with any other genre_id that would be added to the table beyond 25).


File:11239-1190-5.png

We could do the same thing for dates, to query invoices that were not between 2010-01-01 and 2010-12-31.

SELECT *
FROM invoice
WHERE invoice_date NOT BETWEEN '2010-01-01' AND '2010-12-31';

This could also be written as:

SELECT *
FROM invoice
WHERE invoice_date < '2010-01-01' OR invoice_date > '2010-12-31';

Notice that unlike the BETWEEN statement on its own, this example is exclusive of the values due to the NOT, whereas with just the BETWEEN operator, it would include the values.

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The BETWEEN operator allows us to check whether a value is in a range of specified values.