SQL > SQL Commands > Between

Whereas the IN keyword help people to limit the selection criteria to one or more discrete values, the BETWEEN operator is used to select a range. The syntax for the BETWEEN operator is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2';

This will select all rows whose column has a value between 'value1' and 'value2'.

For example, we may wish to select view all sales information between January 6, 1999, and January 10, 1999, from the following table,

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-08-1999

we key in,

SELECT *
FROM Store_Information
WHERE Txn_Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';

Note that date may be stored in different formats in different databases. This tutorial simply choose one of the formats.

Result:

Store_NameSalesTxn_Date
San Diego250Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-08-1999

BETWEEN is an inclusive operator, meaning that 'value1' and 'value2' are included in the result. If we wish to exclude 'value1' and 'value2' but include everything in between, we need to change the query to the following:

SELECT "column_name"
FROM "table_name"
WHERE ("column_name" > 'value1')
AND ("column_name" < 'value2');

We can also use the BETWEEN operator to exclude a range of values by adding NOT in front of BETWEEN. In the above example, if we want to show all rows where the Sales column is not between 280 and 1000, we will use the following SQL:

SELECT *
FROM Store_Information
WHERE Sales NOT BETWEEN 280 and 1000;

Result:

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999

Exercises

For these exercises, assume we have a table called User_Sales with the following data:

Table User_Sales

First_NameLast_NameBirth_DateGenderJoi n_DateTotal_Sales
SophieLeeJan-05-1960FApr-05-2015500
RichardBrownJan-07-1975MApr-05-2015200
JamalSantoOct-08-1983MApr-09-2015350
CaseyHealySep-20-1969MApr-09-201580
JillWilkesNov-20-1979FApr-15-2015210

1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 OR 300;
b) SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 OR 300;
c) SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 AND 300;
d) SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 AND 300;

2. How many records will be returned by the following query?
SELECT * FROM User_Sales WHERE Join_Date BETWEEN 'Apr-05-2015' AND 'Apr-10-2015';

3. How many records will be returned by the following query?
SELECT * FROM User_Sales WHERE Gender = 'F' OR Total_Sales BETWEEN 50 AND 100;

Next: SQL Wildcard




Copyright © 2015   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact