SQL IN Operator

In SQL, the IN operator is useful to search for the specified value matches with any value in the set of multiple values or not.

 

Generally, we will use IN operator with WHERE clause to compare a column or variable values with a set of multiple values.

Syntax for SQL IN Operator

Following is the syntax of IN operator in the 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 any value in the set of multiple values or not and it will return records whatever matched with column1 values.

 

Now, we will check this with the example. For that first, create the “EmployeeDetails” table in the database by using the following SQL statements.

 

create table EmployeeDetails(empid int, empname varchar(50),designation varchar(50),salary int,Location varchar(50))

 

insert into EmployeeDetails

values(1,'suresh','software engineer',25000,'chennai'),

(2,'rohini','AEO',15000,'chennai'),

(3,'madhavsai','business analyst',50000,'nagpur'),

(4,'mahendra','CA',75000,'guntur'),

(5,'sateesh','Doctor',65000,'guntur')

 

select * from EmployeeDetails

Once we execute the above script, our table “EmployeeDetails” will create and result will be as shown below.

 

Newly created employeedetails table in sql server

 Now run the following examples to check IN operator in SQL server.

SQL IN Operator Example

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 the above SQL IN operator query, we will get the result like as shown below.

 

SQL IN Operator Example Result or Output

SQL IN Operator with SubQuery Example

In the following SQL query, we will see how to use subquery with IN operator in the 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 subquery. Following is the result of SQL IN operator with subquery example.

 

SQL IN Operator with SubQuery Example Result or Output

 

Now we will see how to use NOT keyword with IN operator and will check how it will return records. Generally if we use NOT keyword with IN operator it will return all the records that does not exist in the list of values.

SQL IN Operator with NOT Keyword Example

Generally, if we use IN operator in SQL statements that will return records whose column values matches with defined set of values. Suppose if we use NOT keyword with IN operator, then it will return 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 the above SQL IN operator with not keyword result, we will get the result as shown below.

 

SQL Server IN Operator with Not Keyword Example Result or Output