SQL BETWEEN Explained with Practical Examples


Written by - Falguni Thakker
Reviewed by - Deepak Prasad

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

student_id studentname admissionno admissiondate enrollmentno date_of_birth email city class_id
101 reema 10001 02-02-2000 e15200002 02-02-1990 reema@gmail.com surat 2
102 kriya 10002 04-05-2001 e16200003 04-08-1991 kriya@gmail.com surat 1
103 meena 10003 06-05-1999 e15200004 02-09-1989 meena@gmail.com vadodara 3
104 carlin 2001 04-01-1998 e14200001 04-04-1989 carli@gmail.com vapi 1
105 dhiren 2002 02-02-1997 e13400002 02-02-1987 dhiru@gmail.com vapi 2
106 hiren 2003 01-01-1997 e13400001 03-03-1887 hiren@gmail.com surat 2
107 mahir 10004 06-09-2000 e15200003 07-09-1990 mahi@gmail.com vapi 3
108 nishi 2004 02-04-2001 e16200001 03-02-1991 nishi@gmail.com vadodara 1

Result Table

result_id student_id examname examdate subject obtainmark totalmarks percentage grade status
3001 101 sem1 07-08-2001 1 80 100 80 A+ pass
3002 101 sem1 08-08-2001 2 76 100 76 A+ pass
3003 102 sem3 05-05-2000 3 67 100 67 A pass
3004 102 sem3 06-05-2000 4 89 100 89 A+ pass
3005 102 sem3 07-05-2000 5 90 100 90 A+ pass
3006 103 sem5 08-09-1998 6 55 100 55 B pass
3007 103 sem5 09-09-1998 7 30 100 30 D fail
3008 103 sem5 10-09-1998 8 34 100 34 D fail

Subject Table

subjectid facultyname subjectname subjectcode
1 krishna c 1003
2 rahul cpp 1004
3 radha asp 1005
4 meera sql 1006
5 yasoda cloud 1007
6 nadan cg 1008

 

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 Explained with Practical Examples

 

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:

SQL BETWEEN Explained with Practical Examples

 

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 2nd Feb 1990 to 31 Dec 2002

OUTPUT:

SQL BETWEEN Explained with Practical Examples

 

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 BETWEEN Explained with Practical Examples

 

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:

SQL BETWEEN Explained with Practical Examples

 

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 Explained with Practical Examples

 

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

SQL BETWEEN Explained with Practical Examples

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 Explained with Practical Examples

 

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:

SQL BETWEEN Explained with Practical Examples

 

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

 

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on LinkedIn.

Categories SQL

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment

X