SQL SERVER Topic

BETWEEN Operator In SQL
The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

Syntax :-

  

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


Example :-First of all we create the table named is tblStudentRecord and insert some records as shown in below. 

  

CREATE TABLE tblStudentRecord
(
Name varchar(100), Rollno int primary key, Branch varchar(30),Email varchar(100),Address varchar(200)
)


Insert some records :-

INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1001,'Information Technology','sa@gmail.com','A-3033')
INSERT INTO tblStudentRecord VALUES('Er. Reena Tanti',1002,'ECE','err@gmail.com','T-387, Gaya')
INSERT INTO tblStudentRecord VALUES('Chandan kumar',1003,'CSE','chn@gmail.com','Gaya')
INSERT INTO tblStudentRecord VALUES('Nand kishor kumar',1004,'BCA','nk@gmail.com','A-3033,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1005,'MCA','ab@gmail.com','H-837')
INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1006,'CSE','santosh@gmail.com','A-3031')
INSERT INTO tblStudentRecord VALUES('Neha sharma',1007,'MCA','h@gmail.com','D-34,Patna')
INSERT INTO tblStudentRecord VALUES('Pooja',1008,'ECE','pr@gmail.com','A-90, Jaipur, Rajashthan')
INSERT INTO tblStudentRecord VALUES('Jaya Laxmi',1009,'CSE','jl@gmail.com','Kerla')
INSERT INTO tblStudentRecord VALUES('Pakaj Kumar',1010,'BCA','pk@gmail.com','A-30,Darbhanga,Bihar')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1011,'ME','ab1@gmail.com','K-837')
INSERT INTO tblStudentRecord VALUES('Suneeta',1012,'MCA','su@gmail.com','UP')
INSERT INTO tblStudentRecord VALUES('Suman Kumar',1013,'IT','sk@gmail.com','Block-T,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Ajay kumar',1014,'ECE','aj@gmail.com','D-89')




A. The following SQL statement selects all Name, Branch with a Rollno BETWEEN 1002 and 1008 :- 

  

SELECT Name,Branch FROM tblStudentRecord WHERE Rollno BETWEEN 1002 AND 1008




B. BETWEEN Operator with Text Value :-The following SQL statement selects all student records with a Name beginning with any of the letter BETWEEN 'k' and 'r': 

  

SELECT * FROM tblStudentRecord WHERE NAME BETWEEN 'k' AND 'r'




C. NOT BETWEEN Operator :- To display the Name, Branch which are not in the range of roll number example, use NOT BETWEEN: 
  

SELECT Name,Branch FROM tblStudentRecord WHERE Rollno NOT BETWEEN 1002 AND 1008




D. NOT BETWEEN Operator with Text Value :- The following SQL statement selects all student records with a Name beginning with any of the letter NOT BETWEEN 'k' and 'r'. 

  

SELECT * FROM tblStudentRecord WHERE NAME NOT BETWEEN 'k' AND 'r'




E. BETWEEN Operator with IN Example :-The following SQL statement selects all student records with a Rollno BETWEEN 1002 and 1011, but student records with a Branch of 'MCA' should not be displayed: 
  

SELECT * FROM tblStudentRecord WHERE (Rollno BETWEEN 1002 AND 1011) AND NOT Branch IN('MCA')