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,

Advertisement
  • 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_idstudentnameadmissionnoadmissiondateenrollmentnodate_of_birthemailcityclass_id
101reema1000102-02-2000e1520000202-02-1990reema@gmail.comsurat2
102kriya1000204-05-2001e1620000304-08-1991kriya@gmail.comsurat1
103meena1000306-05-1999e1520000402-09-1989meena@gmail.comvadodara3
104carlin200104-01-1998e1420000104-04-1989carli@gmail.comvapi1
105dhiren200202-02-1997e1340000202-02-1987dhiru@gmail.comvapi2
106hiren200301-01-1997e1340000103-03-1887hiren@gmail.comsurat2
107mahir1000406-09-2000e1520000307-09-1990mahi@gmail.comvapi3
108nishi200402-04-2001e1620000103-02-1991nishi@gmail.comvadodara1

Result Table

result_idstudent_idexamnameexamdatesubjectobtainmarktotalmarkspercentagegradestatus
3001101sem107-08-200118010080A+pass
3002101sem108-08-200127610076A+pass
3003102sem305-05-200036710067Apass
3004102sem306-05-200048910089A+pass
3005102sem307-05-200059010090A+pass
3006103sem508-09-199865510055Bpass
3007103sem509-09-199873010030Dfail
3008103sem510-09-199883410034Dfail

Subject Table

subjectidfacultynamesubjectnamesubjectcode
1krishnac1003
2rahulcpp1004
3radhaasp1005
4meerasql1006
5yasodacloud1007
6nadancg1008

 

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

Advertisement
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:

Advertisement

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

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

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 either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment