WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL BETWEEN Operator


The BETWEEN operator is used to select values within a range.


The SQL BETWEEN Operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Database

In this tutorial we will use the database.

Below is a selection from the "Physical" table:

PhysicalID FirstName Height Weight Blood type
1 Sandra Bayer 161 centimeters 94.3 kilograms B+
2 Anja Eichmann 170 centimeters 93.0 kilograms O+
3 Thomas Luft 163 centimeters 83.0 kilograms O+
4 Benjamin Kruger 181 centimeters 94.9 kilograms A-
5 Mathias Kuhn 176 centimeters 80.6 kilograms B+

BETWEEN Operator Example

The following SQL statement selects all products with a Blood type BETWEEN A and O:

Example

SELECT * FROM Physical
WHERE Blood type BETWEEN A AND O;


NOT BETWEEN Operator Example

To display the products outside the range of the previous example, use NOT BETWEEN:

Example

SELECT * FROM Physical
WHERE Blood type NOT BETWEEN A AND O;


BETWEEN Operator with IN Example

The following SQL statement selects all Physical with a Blood type BETWEEN A and O, but products with a PhysicalID of 1,2, or 3 should not be displayed:

Example

SELECT * FROM Physical
WHERE (Blood type BETWEEN A AND O)
AND NOT PhysicalID IN (1,2,3);


BETWEEN Operator with Text Value Example

The following SQL statement selects all Physical with a FirstName beginning with any of the letter BETWEEN 'C' and 'M':

Example

SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';


NOT BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN 'C' and 'M':

Example

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';


Sample Table

Below is a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 7/4/1996 3
10249 81 6 7/5/1996 1
10250 34 4 7/8/1996 2
10251 84 3 7/9/1996 1
10252 76 4 7/10/1996 2

BETWEEN Operator with Date Value Example

The following SQL statement selects all orders with an OrderDate BETWEEN '04-July-1996' and '09-July-1996':

Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;