SQL BETWEEN Operator

SQL BETWEEN Operator is used to filter rows based on range of values in specified column. These values can be numbers, text or dates. Rows can be retrieved not in between the range of values specified column.

SQL BETWEEN Operator Syntax :

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

Sample code for SQL BETWEEN Operator with values as numbers :

In this below SQL statement results are retrieved based on column Price and BETWEEN values of 10 and 20.

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

In this below SQL statement results are retrieved based on column Price and NOT in BETWEEN values of 10 and 20.

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

Sample code for SQL BETWEEN Operator with values as text :

In this below SQL statement results are retrieved based on Column Product Name beginning with any of the letters in BETWEEN ‘C’ and ‘M’

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

In this below SQL statement results are retrieved based on Column Product Name beginning with any of the letters NOT in BETWEEN ‘C’ and ‘M’

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

Sample code for SQL BETWEEN Operator with values as dates :

In this below SQL statement results are retrieved based on Column Order Date in BETWEEN dates specified.

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

Notice that the BETWEEN operator can produce different result in different databases!

In some databases, BETWEEN selects fields that are between and excluding the test values.
In other databases, BETWEEN selects fields that are between and including the test values.
And in other databases, BETWEEN selects fields between the test values, including the first test value and excluding the last test value.

Therefore: Check how your database treats the BETWEEN operator!

Vivek Vengala
 

Vivek Vengala is a Online Entrepreneur, Web Developer from Hyderabad India.

Click Here to Leave a Comment Below 0 comments

Leave a Reply:

x

Join My Facebook Group to Receive Coding Tutorial for Free

email-subsribe