The BETWEEN is an operator that allows us to check if an attribute is 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.
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.
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.
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 March in 2009, we could do the following:
SELECT * FROM invoice WHERE invoice_date BETWEEN '2009-03-01' AND '2009-03-31';
This would be simpler than having two separate parts:
SELECT * FROM invoice WHERE invoice_date >= '2009-03-01' AND invoice_date <= '2009-3-31';
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).
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.
Source: Authored by Vincent Tran