The SQL IN operator is used to check an specified value matches with any value in a list of values. It is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE

Syntax
The syntax for the IN operator in SQL is: 
expression IN (value1, value2, .... value_n);

OR

expression IN (Subquery);
Parameters description
expression
This is a value to match in a list of values. 

value1, value2 ..., alue_n
These are the list of values to test against expression. All list of values must be of the same type as expression. If any of these values match with the expression, then the IN operator will evaluate it as true.

Subquery
It is a SELECT statement that has a result set of one column. This column must have the same data type as expression. Its result set will be tested against expression and if any of its values match with expression, then the IN operator will evaluate to true.

Lets' prepare some data to understand it with an example 
CREATE TABLE TblStudent
(
    SRNo VARCHAR(15) NOT NULL PRIMARY KEY,
    StudentName VARCHAR(50),
    FatherName VARCHAR(50),
    CourseCode VARCHAR(15)
)

GO

INSERT INTO TblStudent(SRNo, StudentName, FatherName, CourseCode) 
VALUES ('SR01','Kamal','Harish','BCA'),
('SR02','Sachin','Gagan','BBA'),
('SR03','Kapil','Raj','MCA'),
('SR04','Tanuj','Sanjeev','BPharma'),
('SR05','Lalit','Shyam','B.Arch')


GO

CREATE TABLE TblCourse
(
    CourseCode VARCHAR(15) NOT NULL PRIMARY KEY,
    CourseName VARCHAR(50),
	CourseFee INT
)

GO

INSERT INTO TblCourse(CourseCode,CourseName,CourseFee) VALUES
('BCA','Bachelor of Computer Applications',50000),
('BBA','Bachelors of Business Administration',60000),
('BPharma','Bachelor of Pharmacy',95000),  
('MCA','Master of Computer Applications' ,85000),
('B.Arch','Bachelor of Architecture',65000)
Result
Question: Write a query to get all the rows from TblStudent table for the students who have StudentName either Sachin or Kapil. 
SELECT StudentName,FatherName
FROM TblStudent
WHERE StudentName IN ('Sachin','Kapil')

OR

SELECT StudentName,FatherName
FROM TblStudent
WHERE StudentName ='Sachin' OR StudentName ='Kapil' 

Note: Both Query will produce the same result as IN operator is replcement of multiple OR conditions.
Question: Write a query to get all the rows from the TblStudent table for the students who have enrolled in 'BAC', 'BBA', and 'MCA' Courses. 
SELECT StudentName,FatherName
FROM TblStudent
WHERE CourseCode IN ('BBA','BCA','MCA')

OR

SELECT StudentName,FatherName
FROM TblStudent
WHERE CourseCode='BBA' OR CourseCode='BCA' CourseCode='MCA'
How to use IN operator with a subquery
The following query will fetch all the students who have enrolled in the courses whose fees are less than 70000. 
SELECT StudentName,FatherName
FROM TblStudent
WHERE CourseCode IN ('BBA','BCA','MCA')

OR

SELECT StudentName,FatherName
FROM TblStudent
WHERE CourseCode='BBA' OR CourseCode='BCA' CourseCode='MCA'
Result
In this example 
  • First, the subquery returned a list of courses whose fees less than 70000. 
  • Second, the outer query will fetch the student's records whose CourseCode matches with the courseCode returned by the subquery.
Note: Subquery runs first, and only once. Subquery result set is used in the execution of outer query.


like operator in sql; in operator in sql; operator in sql; sql in operator; how to use in operator in sql; in operator in sql server; in operator sql; what is in operator in sql;in operator in sql with multiple column