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!