A Guide to the SQL IN Operator
SQL IN operator is used along with WHERE clause for providing multiple values as part of the WHERE clause.
1. SQL IN
SQL IN operator is almost like having multiple OR operators for the same column. Let’s discuss in detail about the SQL IN operator. There are two ways to define IN operator. We will discuss both the ways in details below.
1.1) Multiple Values as Part of IN
Syntax:
SELECT Column(s) FROM table_name WHERE column IN (value1, value2, ... valueN);
Using the above-mentioned syntax, we can define multiple values as part of IN operator. We will understand the above-mentioned syntax in more detail through some examples. Let’s consider the following Student table for example purpose.
| RollNo | StudentName | StudentGender | StudentAge | StudentPercent |
|---|---|---|---|---|
| 1 | George | M | 14 | 85 |
| 2 | Monica | F | 12 | 88 |
| 3 | Jessica | F | 13 | 84 |
| 4 | Tom | M | 11 | 78 |
Scenario: Get the percentage of students whose age is 12 or 13. Query:
SELECT StudentPercent FROM Student WHERE StudentAge IN ('12', '13');
Output:
| StudentPercent |
|---|
| 88 |
| 84 |
1.2) Select Query as Part of IN
Syntax:
SELECT Column(s) FROM table_name WHERE column IN (SELECT Statement);
Using the above-mentioned syntax, we can use SQL SELECT statement for providing values as part of the IN operator. We will understand the above-mentioned syntax in more detail through some examples. Let’s consider the following Product and Supplier table for example purpose.
PRODUCT Table
| ProductId | ProductName | ProductPrice |
|---|---|---|
| 1 | Cookie | 10 |
| 2 | Cheese | 11 |
| 3 | Chocolate | 15 |
| 4 | Jam | 20 |
SUPPLIER Table
| ProductId | ProductName | SupplierName |
|---|---|---|
| 1 | Cookie | ABC |
| 2 | Cheese | XYZ |
| 3 | Chocolate | ABC |
| 4 | Jam | XDE |
Scenario: Get the price of the product where the supplier is ABC. Query:
SELECT ProductPrice FROM Product WHERE ProductName IN (SELECT ProductName FROM Supplier WHERE SupplierName = "ABC");
Output:
| ProductPrice |
|---|
| 10 |
| 15 |
1.3) SQL Nested IN
We can also use IN inside other IN operator. To understand it better, let’s consider the below-mentioned scenario.
Scenario: Get the price of the product where the supplier is ABC and XDE. Query:
SELECT ProductPrice FROM Product WHERE ProductName IN (SELECT ProductName FROM Supplier WHERE SupplierName IN ( "ABC", "XDE" ));
Output:
| ProductPrice |
|---|
| 10 |
| 15 |
| 20 |
2. SQL NOT IN
SQL NOT IN operator is used to filter the result if the values that are mentioned as part of the IN operator is not satisfied. Let’s discuss in detail about SQL NOT IN operator.
Syntax:
SELECT Column(s) FROM table_name WHERE Column NOT IN (value1, value2... valueN);
In the syntax above the values that are not satisfied as part of the IN clause will be considered for the result. Let’s consider the earlier defined Student table for example purpose.
Scenario: Get the percentage of students whose age is not in 12 or 13. Query:
SELECT StudentPercent FROM Student WHERE StudentAge NOT IN ('12', '13');
Output:
| StudentPercent |
|---|
| 85 |
| 78 |
2.1) Select Query as Part of SQL NOT IN
Syntax:
SELECT Column(s) FROM table_name WHERE column NOT IN (SELECT Statement);
Using the above-mentioned syntax, we can use SELECT statement for providing values as part of the IN operator. We will understand the above-mentioned syntax in more detail through some examples. Let’s consider the earlier defined Product and Supplier table for example purpose.
Scenario: Get the price of the product where the supplier is not ABC. Query:
SELECT ProductPrice FROM Product WHERE ProductName NOT IN (SELECT ProductName FROM Supplier WHERE SupplierName = "ABC");
That’s all for SQL IN and SQL NOT IN operator examples. A Guide to the SQL IN Operator
