Using the SQL IN Operator: A Comprehensive Guide
☰Fullscreen
Table of Content:
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
Syntax:
<span class="pln"> SELECT column_name</span><span class="pun">(</span><span class="pln">s</span><span class="pun">)</span><span class="pln"> FROM table_name WHERE column_name IN </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">...);</span><span class="pln"> </span>
You can use this below syntax also
Syntax:
<span class="pln"> SELECT column_name</span><span class="pun">(</span><span class="pln">s</span><span class="pun">)</span><span class="pln"> FROM table_name WHERE column_name IN </span><span class="pun">(</span><span class="pln">SELECT STATEMENT</span><span class="pun">);</span><span class="pln"> </span>
Prerequisite Codes
If you don't have table please create below table and practice it.
My database name is SQLExamples
<span class="pln"> USE </span><span class="typ">SQLExamples</span><span class="pln"> </span>
Create this below Employee table
<span class="pln"> DROP TABLE </span><span class="typ">Employee</span><span class="pln"> CREATE TABLE </span><span class="typ">Employee</span><span class="pun">(</span><span class="pln"> </span><span class="typ">EmpId</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="typ">EmpName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">25</span><span class="pun">),</span><span class="pln"> </span><span class="typ">EmpAddress</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln"> </span><span class="typ">EmpDept</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">25</span><span class="pun">)</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span>
Insert data inside table
<span class="pln"> INSERT INTO </span><span class="typ">Employee</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rambo'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rohit'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rohon'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ITIS'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ronok'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ITIS'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rubin'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ITIS'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sorif'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Soriful'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">8</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sofik'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">)</span><span class="pln"> </span>
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee</span><span class="pln"> </span>
Output:
The above code will produce the following result-
|
EmpId |
EmpName |
EmpAddress |
EmpDept |
|
1 |
Rambo |
Kolkata |
IT |
|
2 |
Rohit |
Kolkata |
IT |
|
3 |
Rohon |
Kolkata |
ITIS |
|
4 |
Ronok |
Kolkata |
ITIS |
|
5 |
Rubin |
Kolkata |
ITIS |
|
6 |
Sorif |
Kolkata |
ADMIN |
|
7 |
Soriful |
Kolkata |
ADMIN |
|
8 |
Sofik |
Kolkata |
ADMIN |
Example 1:
Code:
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee</span><span class="pln"> WHERE </span><span class="typ">EmpDept</span><span class="pln"> IN </span><span class="pun">(</span><span class="str">'IT'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">)</span><span class="pln"> </span>
Output:
The above code will produce the following result-
|
EmpId |
EmpName |
EmpAddress |
EmpDept |
|
1 |
Rambo |
Kolkata |
IT |
|
2 |
Rohit |
Kolkata |
IT |
|
6 |
Sorif |
Kolkata |
ADMIN |
|
7 |
Soriful |
Kolkata |
ADMIN |
|
8 |
Sofik |
Kolkata |
ADMIN |
Example 2:
Code:
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee</span><span class="pln"> WHERE </span><span class="typ">EmpDept</span><span class="pln"> NOT IN </span><span class="pun">(</span><span class="str">'IT'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">)</span><span class="pln"> </span>
Output:
The above code will produce the following result-
|
EmpId |
EmpName |
EmpAddress |
EmpDept |
|
3 |
Rohon |
Kolkata |
ITIS |
|
4 |
Ronok |
Kolkata |
ITIS |
|
5 |
Rubin |
Kolkata |
ITIS |
Example:
Code:
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee</span><span class="pln"> WHERE </span><span class="typ">EmpDept</span><span class="pln"> IN </span><span class="pun">(</span><span class="pln"> SELECT </span><span class="typ">EmpDept</span><span class="pln"> FROM </span><span class="typ">Employee</span><span class="pln"> WHERE </span><span class="typ">EmpName</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Rambo'</span><span class="pln"> OR </span><span class="typ">EmpName</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Sorif'</span><span class="pun">)</span><span class="pln"> </span>
Output:
The above code will produce the following result-
|
EmpId |
EmpName |
EmpAddress |
EmpDept |
|
1 |
Rambo |
Kolkata |
IT |
|
2 |
Rohit |
Kolkata |
IT |
|
6 |
Sorif |
Kolkata |
ADMIN |
|
7 |
Soriful |
Kolkata |
ADMIN |
|
8 |
Sofik |
Kolkata |
ADMIN |
No Questions Data Available.
No Program Data.