SQL IN Operator

In SQL, the IN operator is useful to search for the specified value that 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 value with multiple values.

Syntax of 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 the 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 that match 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 the following is the result.

 

Newly created employeedetails table in sql server

 Now run the following examples to check IN operator in the 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')

We will get the following result when we execute the above SQL IN operator query.

 

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 match with the results returned by the SQL subquery. Following is the result of the SQL IN operator with a subquery example.

 

SQL IN Operator with SubQuery Example Result or Output

 

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

SQL IN Operator with NOT Keyword Example

Generally, using IN operator in SQL statements will return records whose column values match with a defined set of values. Suppose if we use NOT keyword with IN operator, it will return data whose column values do 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 below result.

 

SQL Server IN Operator with Not Keyword Example Result or Output

Previous
 
Next