The BETWEEN is an operator that allows us to check if an attribute is within a range of values. It is similar to using two separate conditions using the logical operators, but helps to simplify things 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. Therefore, no rows could match that criteria, which is why we would get the following result:
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 to return the opposite result set. Using the example from the prior tutorial, if we wanted to get the tracks that had the genre_id NOT being between 10-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 may 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.
[MUSIC PLAYING] Here, we'll be covering the BETWEEN operator. The BETWEEN operator allows us to search the area based on specific ranges. So for example here, we're looking at the invoice table, looking for the customer ID of the value greater or equal to 2, and then the customer ID of less than or equal to 5. This gives us a range of 2 to 5 for all the customers. So in this case here, all those items will be in the place.
However, this can get a little bit lengthy. You can simplify this by utilizing the BETWEEN operator by changing this out. Say between 2 and 5. Between those two it would work exactly the same way. It simplifies a query. You can make the changes to this without having to worry about those details. One thing to note is that you do want to make sure the value that is the smallest appears first, the value appears last should be the larger value.
If we made a switch between the two, it will return no data in this case here. Note that we can also utilize this for dates. So for example, if we have here for the invoice date, it's greater than a certain date. And then also less than a certain date finding all those invoices in this case here returns eight invoices. We can do the same thing as well, utilizing the between. We can remove everything there. If we run that, it return the same values as well.
[MUSIC PLAYING]
Source: Authored by Vincent Tran
