SQL Between, MySQL Between Dates, Not Between

The SQL BETWEEN operator is used along with WHERE clause for providing a range of values. The values can be the numeric value, text value, and date. SQL BETWEEN operator is almost like SQL IN operators used in a sequential manner. The values are defined as part of the BETWEEN range are inclusive i.e. the values that are mentioned in the range are included at the start and end values. Let’s discuss in detail about the BETWEEN operator. As mentioned above BETWEEN operator can be used along with numeric value, text value, and date. We will discuss all the three in detail below.

SQL Between Syntax

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

Using the above-mentioned syntax, we can define values as part of BETWEEN operator. Also, the syntax mentioned above remains the same for usage with a numeric value, text value, and date value.

SQL BETWEEN operator for Numeric value

We will understand the above-mentioned syntax in more detail through some examples for numeric value. Let’s consider the following Student table for example purpose.

I am using MySQL database and here is the script to create and insert example records in the Student table.

CREATE TABLE `Student` (
  `rollno` int(11) unsigned NOT NULL,
  `studentname` varchar(20) DEFAULT NULL,
  `studentgender` varchar(5) DEFAULT NULL,
  `studentage` int(3) DEFAULT NULL,
  `studentpercent` int(3) DEFAULT NULL,
  `admissiondate` date DEFAULT NULL,
  PRIMARY KEY (`rollno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Student` (`rollno`, `studentname`, `studentgender`, `studentage`, `studentpercent`, `admissiondate`)
VALUES
	(1, 'George', 'M', 14, 85, '2018-01-01'),
	(2, 'Monica', 'F', 12, 88, '2018-01-31'),
	(3, 'Jessica', 'F', 13, 84, '2018-01-15'),
	(4, 'Tom', 'M', 11, 78, '2017-12-15');

Scenario: Get the percentage of students whose age is between 11 and 13.

SELECT StudentPercent FROM Student WHERE StudentAge BETWEEN 11 AND 13;

Output:

SQL NOT BETWEEN Operator for Numeric Value

The SQL NOT BETWEEN operator is used for getting the values as part of result set which is outside of the range specified by the BETWEEN operator. Scenario: Get the percentage of students whose age is not between 11 and 13.

SELECT StudentPercent FROM Student WHERE StudentAge NOT BETWEEN 11 AND 13;

Output:

SQL BETWEEN operator for Text value

Scenario: Get the RollNo, StudentName and StudentAge where StudentName is between George and Jessica.

SELECT RollNo, StudentName, StudentAge FROM Student WHERE StudentName BETWEEN 'George' AND 'Jessica';

Output:

SQL NOT BETWEEN Operator for Text Value

Scenario: Get the RollNo, StudentName and StudentAge where StudentName is not between George and Jessica.

SELECT RollNo, StudentName, StudentAge FROM Student WHERE StudentName NOT BETWEEN 'George' AND 'Jessica';

Output:

SQL BETWEEN operator for Date value

Scenario: Get the age of students whose admission is between 1st Jan 2018 and 31st Jan 2018.

SELECT StudentAge FROM Student WHERE admissiondate BETWEEN str_to_date('2018-01-01', '%Y-%m-%d') AND '2018-01-31';

Output:

Note that I am using MySQL native function str_to_date to convert string to date. If the string is in default format, we can use it as-is too, just as I have used for the second argument. If you are using Oracle DB, then corresponding function is TO_DATE.

SQL NOT BETWEEN Operator for Date Value

Scenario: Get the age of students whose admission is not between 1st Jan 2018 and 31st Jan 2018.

SELECT StudentAge FROM Student WHERE admissiondate NOT BETWEEN str_to_date('2018-01-01', '%Y-%m-%d') AND '2018-01-31';

Output:

MULTIPLE BETWEEN operators

We can use multiple between operators too. Its syntax is:

SELECT Column(s) FROM table_name WHERE 
column_name BETWEEN value1 AND value2 
AND 
column_name BETWEEN value3 and value4 
... 
AND 
BETWEEN column_name BETWEEN valueN and valueM;

Using the above-mentioned syntax, we can use multiple BETWEEN operators. Scenario: Get the student name with age between 10 and 13 and marks between 80 to 85 percentage.

SELECT StudentName FROM Student WHERE 
StudentAge BETWEEN 10 AND 13 
AND
StudentPercent BETWEEN 80 AND 85;

Output: