SQL BETWEEN Explained with Practical Examples

Overview of SQL Between Operator

The SQL Between is a comparison operator used to check value or an expression is intermediate to the specifies range with where clause, the value or expression which compare with where clause must be less than or equal to some value and greater than or equal to some other value, is basically used to simplify SQL Where clause, the values can be text, date, or numbers. It can be used in a SELECT, UPDATE, or DELETE statement

• SQL Where Between statement returns values within a given range
• SQL Where Between is a shorthand of the two conditions >= AND <=
• SQL Between is inclusive, that is start and end values are included in the range of comparison

 

SQL Between Syntax

test_expression | column_name  [ NOT]  BETWEEN  start_value  AND  end_value

Here,

• test_expression | column_name: it is an expression or column name which value to test in the range defined by start_value and end_value. test_expression must be the same data type as both begin_value and end_value
• NOT: it is an optional argument, specifies that the result of the predicate be negated
• start_value: it is any valid expression specifies starting value of the range, start_value must be the same data type as both test_expression and end_value
• end_value: It is any valid expression that specifies the ending value of range, end_value must be the same data type as both test_expression and start_value.
• AND: Acts as a placeholder that indicates test_expression should be within the range indicated by start_value and end_value

 

Return Type of SQL Between Operator

SQL Between is a comparison operator used with the SQL where clause so the result type of SQL Between is Boolean either True or False

 

Return Value of SQL Between Operator

• SQL Between returns true if the value of test_expression is greater than or equal to the value of begin_value and less than or equal to the value of end_value
• NOT BETWEEN returns true if the value of test_expression is less than the value of begin_value or greater than the value of end_value

 

SQL Between Examples

Consider student result management database with three tables, tblstudent, tblresult and tblsubject for practical examples
Student Table

Result Table

Subject Table

 

SQL Between Range with Numeric values

Example-1: Write SQL Query to extract students result from data who have obtained marks in the range of 50 to 90

SELECT tblstudent.studentname, tblresult.examname, tblresult.examdate, tblresult.obtainmark, tblresult.pecentage
FROM  tblresult LEFT OUTER JOIN tblstudent 
ON tblstudent.student_id = tblresult.student_id
WHERE (tblresult.obtainmark BETWEEN 50 AND 90)

• In the previous query, the SQL Between operator is applied on the value of obtaining mark column to check and to fetch those students records who have obtained greater than or equal to 50 and less than equal to 90 marks
• SQL Left Outer join is used to join two tables student and result to fetch combined result of student detail with the result

OUTPUT:

 

SQL Between operator with Date Range

Example 2 : Write SQL Query to retrieve students list who has given exam between the year of 1998 to 2000

SELECT tblstudent.studentname, FORMAT(tblresult.examdate, 'dd-mm-yyyy') AS 'Exam Date', tblresult.examname, tblresult.obtainmark, tblresult.pecentage, tblresult.subjectid
FROM  tblresult LEFT OUTER JOIN tblstudent 
ON tblstudent.student_id = tblresult.student_id
WHERE        (YEAR(tblresult.examdate) BETWEEN 1998 AND 2000)

• In the above SQL Query, the SQL Between operator is used to compare the date range of the exam
• To extract the year from examdate column SQL YEAR function is applied on examdate and then check the range of exam year between 1998 to 2000

OUTPUT:

 

Example 3: Write SQL Query using between operator to list out student details who has taken admission in between the year 1990 to 2002 and living in Surat city

SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM tblstudent
WHERE(city = 'surat') AND (admissiondate BETWEEN CAST('19990101' AS date) AND CAST('20021231' AS date))

• In this query, the SQL between is applied with the admission date column to examine values that should be in the range of 01/02/1999 to 31/12/2002
• SQL Cast function is used to explicitly convert the data type of an expression, to convert date expression in the range values SQL Cast function is applied
• The query returns student details who has taken admission from 2^nd Feb 1990 to 31 Dec 2002

OUTPUT:

 

SQL Between with the text value

SQL Between statement can also be used to retrieve records by comparing the character of strings, it compares character by character of a string

Example-4: Write SQL query to fetch student details who has city name between Ahmedabad to Vadodara

SELECT  student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM tblstudent
WHERE (city BETWEEN 'ahmedabad' AND 'vadodara')

• In the previous query, SQL Between operator is applied on text datatype column city
• City column values are compared character by character start with an as the first character to v as a first character
• The output of this query contains students’ records whose city name starts from a to v

OUTPUT:

 

SQL NOT BETWEEN

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

Example-5: Write SQL query to display student details with result who are not achieved percentage greater than 70 and less than 90

SELECT  tblstudent.studentname, tblresult.examdate AS 'Exam Date', tblresult.examname, tblresult.obtainmark, tblresult.pecentage, tblresult.subjectid
FROM  tblresult LEFT OUTER JOIN tblstudent ON tblstudent.student_id = tblresult.student_id
WHERE (tblresult.pecentage NOT BETWEEN 70 AND 90)

• In above query, to retrieve records from two tables SQL Left outer join is used to join two tables
• SQL NOT BETWEEN Operator is used to neglect comparison range of 70 to 90

OUTPUT:

 

Multiple Between operators

SQL Multiple Between Syntax

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

 

SQL Multiple Between Examples

Example 6: Use SQL multiple Between operator to fetch student records who have given exam of semester 1 to 2 and achieved percentage more than 70 and less than 90

SELECT tblstudent.studentname, tblresult.examdate AS 'Exam Date', tblresult.examname, tblresult.obtainmark, tblresult.pecentage, tblresult.subjectid
FROM tblresult LEFT OUTER JOIN tblstudent ON tblstudent.student_id = tblresult.student_id
WHERE  (tblresult.pecentage BETWEEN 70 AND 90) AND (tblresult.examname BETWEEN 'sem1' AND 'sem2')

• In above query two SQL Between operator is used to compare two range of values with two different columns percentage and examname
• The resulting records of this query contains student details who has got percentage between 70 to 90, and with examname of sem1 to sem2

OUTPUT:

 

SQL Between with Update statement

SQL Between operator can also be used with SQL Update statement to modify records based on condition specified in WHERE clause with the range value to compare

Example-7: Modified student class details set to class id as 4 who got admission in the year between 1998 to 1999

Resulting records set before updation

UPDATE tblstudent SET class_id = 4
WHERE (YEAR(admissiondate) BETWEEN 1998 AND 1999)

• After executing above query two records will be updated whose admission year is 1998 and 1999, Class_id column value will be modified and set to 3
• To check the modified record SQL, select statement is used to fetch all records of student table

SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM  tblstudent

OUTPUT:

 

SQL Between with Delete statement

SQL Between Operator can be used with SQL Delete statement to remove records from table based on comparison condition within the given range value

Example-8: Write SQL Query to remove student result data who have obtained marks in the range of 50 to 60

DELETE FROM tblresult
WHERE (obtainmark BETWEEN 50 AND 60)

• In this query SQL Between operator is used compare a range of obtaining marks with where clause
• After executing the SQL Delete statement one record with marks 55 will be removed from tblresult
• To check the result of the above query we need to use SQL Select statement to fetch all records of tblresult

SELECT result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status
FROM tblresult

OUTPUT:

 

Summary

This Article of SQL Between we covered an overview of SQL Between operator its usages, Syntax of SQL Between operator and Practical examples with SQL Select statement, SQL Between operator is applied with Where clause as a comparison operator to check condition, first we have given example with numerical range to compare, then comparison with text value and date range, in the end of this article practical example of SQL Between with Update and Delete statement is covered

 

References

SQL Delete Row
SQL Left Join

 

Read More

SQL BETWEEN