SQL Between

The SQL BETWEEN Operator

The BETWEEN operator is used in the WHERE clause to select values within a specified range.

The range is inclusive - the beginning and end values of the range are included in the results.

The values can be numbers, text, or dates.

Example

 SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

Syntax

<p><code class="sqlHigh">
SELECT <em>column_name(s)</em><br/>
FROM <em>table_name</em><br/>
WHERE <em>column_name </em>BETWEEN <em>value1</em> AND <em>value2;</em></code></p>

Demo Database

Below is a selection from the Products table used in the examples:

NOT BETWEEN

The NOT BETWEEN operator is used in the WHERE clause to select values outside a specified range.

The following SQL returns all products with a price NOT between 10 and 20:

Example

 SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

BETWEEN with IN

The following SQL returns all products with a price between 10 and 20. In addition, the CategoryID must be either 1, 2 or 3:

Example

 SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID IN (1,2,3);

BETWEEN Text Values

The following SQL selects all products with a ProductName alphabetically between 'Geitost' and 'Louisiana Hot Spiced Okra':

Example

SELECT * FROM Products

WHERE ProductName BETWEEN 'Geitost' AND 'Louisiana Hot Spiced Okra'

ORDER BY ProductName;

NOT BETWEEN Text Values

The following SQL selects all products with a ProductName NOT between 'Geitost' and 'Louisiana Hot Spiced Okra':

Example

 SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Geitost' AND
  'Louisiana Hot Spiced Okra'
ORDER BY ProductName;

BETWEEN Dates

The BETWEEN operator is useful for filtering records within a specific date or time period. Ensure the date format matches the database (e.g. 'YYYY-MM-DD').

The following SQL selects all orders placed in July, 1996:

Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

Sample Table

Below is a selection from the Orders table used in the example above: