SQL IN Operator, SQL NOT IN Operator

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 Email
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 Email
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 Email
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 Email
2 Ram Surat ram@yahoo.com
4 Vaidehi Ahmedabad meera@yahoo.com

Leave a Reply

Your email address will not be published. Required fields are marked *

6 + 4 =


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">