Sometimes you’re looking for IDs, prices, zip codes and the like in a range. You can use the BETWEEN operator to select integers, text, or dates within a range of items. The syntax when using the BETWEEN operator would look similar to the following:
SELECT column(s)
FROM table
WHERE column_name BETWEEN value AND value;
In this article, we’ll use the same “Customers” table from our first articles:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
The following SQL statement selects all Sites with an ID BETWEEN 2 and 4:
SELECT * FROM Customers
WHERE ID BETWEEN 2 AND 4;
Or look for the ones that are not between those:
SELECT * FROM Customers
WHERE ID NOT BETWEEN 2 AND 4;
One of the best aspects of BETWEEN is the ability to specify multiples. So you can look for ID between 2 and 4 and not 3 and 4:
SELECT * FROM Customers
WHERE (ID BETWEEN 2 AND 4)
AND NOT ID IN (3,4);
The following SQL statement selects all Sites with a Site beginning with any of the letter BETWEEN ‘M’ and ‘Z’:
SELECT * FROM Customers
WHERE Site BETWEEN 'M' AND 'Z';
Or the same, using NOT BETWEEN ‘A’ and ‘L’:
SELECT * FROM Customers
WHERE Site NOT BETWEEN 'A' AND 'L';
If there was a Date field, the following statement would select all Sites with a Date BETWEEN ’01-01-2010′ and ’01-01-2016′:
SELECT * FROM Customers
WHERE Date BETWEEN #01/01/2010# AND #01/01/2016#;
At this point, we have lots of logic we can use to further find data in large databases. We’ll also need to eventually get to joining tables, so we can take use of multiple pieces of data.