INDEX: SQL Tutorial

SQL IN Operator

SQL IN operator

This SQL tutorial explains how to use IN operator in SQL.SQL IN condition is also termed as SQL IN operator , It easily allow us to test if the expression fulfillĀ  the condition.The ‘IN’ operator belongs to the same family as of ‘OR’ operator.SQL ‘IN’ operator are treated as a shorthand for multiple ‘OR’ condition.

Let us look at the syntax of ‘IN’ operator:

Syntax(1):

SELECT column1,.....column n 
FROM table_name
WHERE column_name IN(values1,values2,values3);

Syntax(2):

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

Following syntax shows the format in which ‘IN’ operators are used.’IN’ operator are also used with ‘WHERE’ clause.


Examples on SQL IN operator:

As already discussed SQL ‘IN’ operator are shorthand for multiple ‘OR’ operators.Now in this section , we will learn the practical use of IN operator though various examples.

Table name:Harsh

S.NO State Area Literacy rate(%) Region
1 Kerala 33863 93.91 Southern
2 Maharashtra 307713 83.78 Western
3 Rajasthan 342239 67.06 Western
4 Odisha 155707 73.45 Eastern
5 West Bengal 88752 77.08 Eastern
6 Jharkhand 79714 67.63 Eastern

Example 1:

SELECT * FROM Harsh
WHERE State IN ('Kerala','Maharashtra','Rajasthan');

Chooses the values from state-‘Kerala’,’Maharashtra’,’Rajasthan’

S.NO State Area Literacy rate(%) Region
1 Kerala 33863 93.91 Southern
2 Maharashtra 307713 83.78 Western
3 Rajasthan 342239 67.06 Western

Example 2:

SELECT * 
FROM Harsh
WHERE state NOT IN ('Kerala','Maharashtra','Rajasthan';

Just opposite to the example 1, Choose values where state doesn’t belong to the following mentioned in example.

4 Odisha 155707 73.45 Eastern
5 West Bengal 88752 77.08 Eastern
6 Jharkhand 79714 67.63 Eastern

Example 3:

SELECT * FROM Harsh
WHERE State IN (SELECT State FROM Harsh WHERE State='Rajasthan');
3 Rajasthan 342239 67.06 Western

This example is base on syntax ‘2’ mentioned above. IN(SELECT….) In could be followed by select statement(@syntax-2). All the values will be fetched with state =’rajasthan’.

Hope that you like the article , please drop your doubts ,questions in comment section down below. Cheers!

Leave a Comment

Your email address will not be published. Required fields are marked *