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!