SQL BETWEEN Operator
SQL > SQL Commands >
The BETWEEN operator is used when the filtering criteria is a continuous range with a maximum value and a minimum value. It is always used in the WHERE clause.
The syntax for the BETWEEN operator is as follows:
WHERE "column_name" BETWEEN 'value1' AND 'value2';
This will select all rows whose column has a value between 'value1' and 'value2.'
We use the following table for our example.
To select view all sales information between January 6, 1999, and January 10, 1999, we key in,
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.
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:
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:
WHERE Sales NOT BETWEEN 280 and 1000;
For these exercises, assume we have a table called User_Sales with the following data:
1. Which of the following SQL statement is valid? (There can be more than one answer)
2. How many records will be returned by the following query?
3. How many records will be returned by the following query?