SQL IN Operator
SQL IN Operator – The SQL IN operator used to select column with more than one value in WHERE condition.
The IN operator allow us to specify the more than one value in Where condition.
In last sql post we leaned LIKE Operator, in this sql tutorial we will learn SQL IN Operator with syntax and example.
The IN Operator Syntax :
SELECT col1, col2 FROM table_name WHERE col1 IN (val1,val2,val3)
col1, col2 – are the name of columns which is selected in result.
table_name – is name of table from records to be selected.
val1,val2,val3 – are the values which match with col1 then displayed in result.
The SQL IN Operator Example :
Here, we have table Student and we want to select records whose id is 1 or 3, or 4.
Here, we need to make sql query which returns student whose id is 1 or 3 or 4.
We can use OR operator in where condition instead of IN operator.
Here, we have table Student with below records.
The SQL Student Table :
| ID | Name | City | |
|---|---|---|---|
| 1 | Meera | Bombay | meera@yahoo.com |
| 2 | Ram | Surat | ram@yahoo.com |
| 3 | Manoj | Bombay | manoj@yahoo.com |
| 4 | Vaidehi | Ahmedabad | meera@yahoo.com |
SELECT * FROM Student WHERE ID IN (1, 3, 4)
The Result of SQL IN Operator is :
| ID | Name | City | |
|---|---|---|---|
| 1 | Meera | Bombay | meera@yahoo.com |
| 3 | Manoj | Bombay | manoj@yahoo.com |
| 4 | Vaidehi | Ahmedabad | meera@yahoo.com |
Here, we can use OR operator same for above example.
SELECT * FROM Student WHERE ID = 1 OR ID = 3 OR ID = 4
The Result of SQL OR Operator is :
| ID | Name | City | |
|---|---|---|---|
| 1 | Meera | Bombay | meera@yahoo.com |
| 3 | Manoj | Bombay | manoj@yahoo.com |
| 4 | Vaidehi | Ahmedabad | meera@yahoo.com |
SQL NOT IN Operator – The NOT IN operator used to returns values except the column values match with more than one value in where condition.
The NOT IN is contradictory to IN Operator.
The NOT IN Operator Syntax :
SELECT col1, col2 FROM table_name WHERE col1 NOTIN (val1,val2,val3)
val1,val2,val3 – are the values which match with col1 then not displayed in result.
The NOT IN Operator Example
SELECT * FROM Student WHERE ID NOT IN (1, 3)
The Result of SQL NOT IN Operator is :
| ID | Name | City | |
|---|---|---|---|
| 2 | Ram | Surat | ram@yahoo.com |
| 4 | Vaidehi | Ahmedabad | meera@yahoo.com |