SQL IN Operator with Subquery and Not Keyword Examples

  Total Views : 397
  Comments : 0
 
  Keywords : SQL in operator example, Use of in operator in sql query with example, SQL server in operator with where clause in stored procedure, SQL in operator syntax and declaration example

Here we will learn what is in operator in sql server and how to use sql in operator in queries or stored procedures with example.

SQL IN Operator

The IN operator in SQL is used to search for specified value matches any value in set of multiple values. Generally we will use this IN operator in WHERE clause to compare column or variable values with set of multiple values. Syntax for IN operator in SQL will be like as shown below 

Syntax for SQL IN Operator

Following sql statement is the syntax of sql in operator

 

SELECT column1, column2 FROM tablename WHERE column1 IN ('value1','value2','value3') 

 If you observe above SQL IN operator syntax we are comparing column1 value matches any value in set multiple values. It will return records whatever matched column1 values with set of values. We will check this with example for that first create “EmployeeDetails” table by using following script

 

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 run above script our table “EmployeeDetails” will create and result will be like as shown below

 

Newly created employeedetails table in sql server

 Now run following examples to check IN operator in SQL

SQL IN Operator Example

The following SQL query will return all employees where location in 'chennai', 'guntur', 'bangalore'

 

SELECT * FROM EmployeeDetails WHERE Location IN('chennai','guntur','bangalore')

Once we run above sql query we will get sql in operator result will be like as shown below

 

Output

 

Following is the output of sql in operator example

 

SQL IN Operator Example Result or Output

SQL IN Operator with SubQuery Example

In following SQL query we will see how to use subquery with IN operator in SQL

 

SELECT * FROM EmployeeDetails WHERE empname IN(SELECT empname FROM EmployeeDetails Where empid in(1,4,5)) 

The above SQL query will return all employees where empname in results returned by SQL sub query. Our result table will be like as shown below

 

Output

 

Following is the output 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 exists in list of values.

SQL IN Operator with NOT Keyword Example

Generally in SQL statement if we use IN operator it will return data where column value in set of values. Suppose if we use NOT keyword with IN operator it will return data where column value not in set of values.

 

The following SQL query will return all the records where the location not in mentioned values

 

SELECT * FROM EmployeeDetails WHERE Location NOT IN('chennai','guntur','bangalore')

 Once we run above sql query we will get sql in operator with not keyword result will be like as shown below

 

Output

 

Following is the output of sql in operator with not keyword example

 

SQL Server IN Operator with Not Keyword Example Result or Output

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User