In sql, IN operator is used to search for specified value matches with any value in set of multiple values or not.
Generally, we will use IN operator with WHERE clause to compare a column or variable values with set of multiple values.
Following is the syntax of in operator in sql server.
SELECT column1, column2 FROM tablename WHERE column1 IN ('value1','value2','value3')
If you observe above sql IN operator syntax, we are checking whether the column1 value matches with any value in set of multiple values or not and it will return records whatever matched with column1 values.
Now, we will check this with example for that first create “EmployeeDetails” table in database by using following sql statements.
create table EmployeeDetails(empid int, empname varchar(50),designation varchar(50),salary int,Location varchar(50))
insert into EmployeeDetails
select * from EmployeeDetails
Once we execute above script, our table “EmployeeDetails” will create and result will be like as shown below.
Now run following examples to check IN operator in sql server.
The following sql query will return all the employees whose location in 'chennai', 'guntur', 'bangalore'.
SELECT * FROM EmployeeDetails WHERE Location IN('chennai','guntur','bangalore')
When we execute above sql in operator query, we will get the result like as shown below.
In following sql query we will see how to use subquery with IN operator in sql server.
SELECT * FROM EmployeeDetails WHERE empname IN(SELECT empname FROM EmployeeDetails Where empid in(1,4,5))
The above sql query will return all the employees whose empname values matches with the results returned by sql sub query. Following is the result of sql in operator with subquery example.
Now we will see how to use NOT keyword with IN operator and will check how it will return a records. Generally if we use NOT keyword with IN operator it will return all the records that does not exists in the list of values.
Generally, if we use IN operator in sql statements that will return a records whose column values matches with defined set of values. Suppose if we use NOT keyword with IN operator, then it will return a data whose column values not match with the set of values.
The following sql query will return all the records whose location is not in mentioned values.
SELECT * FROM EmployeeDetails WHERE Location NOT IN('chennai','guntur','bangalore')
Once we execute above sql in operator with not keyword result, we will get the result like as shown below.