SQL Server IN Condition

SQL Server 'IN' condition is used to avoid multiple or conditions in the query.'IN' condition accept several values.Each value supplied  in the condition are compared with the data, and if  the comparison is success then the data comes to output.

 

CREATE TABLE student

(

       name VARCHAR(50) NOT NULL,

       roll INT NOT NULL,

       adress VARCHAR(50),

       age INT NOT NULL,

);

 

INSERT INTO student(name,roll,adress,age)

SELECT 'John1',123,'34,school street',12

UNION

SELECT 'John2',124,'35,school street',13

UNION

SELECT 'John3',125,'36,school street',14

UNION

SELECT 'John4',126,'38,school street',15

UNION

SELECT 'John5',127,'39,school street',16

UNION

SELECT 'John6',128,'40,school street',17

Here is example of 'IN' condition multiple with string  value. 

      SELECT name,roll,adress, age

      FROM student WHERE name IN ('John3','John5','John6')

Output

name     roll   adress              age   

John3    125    36,school street    14
John5    127    39,school street    16
John6    128    40,school street    17

Here is a 'OR' condition query gives the same output.

        SELECT name,roll,adress, age

        FROM student

        WHERE name='John3'

        OR name='John5'

        OR name='John6'

Output 

name     roll   adress              age   

John3    125    36,school street    14
John5    127    39,school street    16
John6    128    40,school street    17

It should be noted that 'IN' condition can hold a number of supplied value , but it should not be the large, if large number is value supplied , internal memory exception may occur. 








Here is example of 'IN' condition with integer value.

      SELECT name,roll,adress, age

        FROM student WHERE age IN (12,14,17)

Output 

name     roll   adress              age   

John3    125    36,school street    14
John5    127    39,school street    16
John6    128    40,school street    17

'IN' condition accept  sub query, you can write query from other table in the condition. 


Here is example of in  condition with sub query

           SELECT name,

                                    roll,

                                    adress,

                                    age

                                    FROM student

                                     WHERE age IN

                                     (

                                                   SELECT age

                                                   FROM student WHERE age=12

                                     )

Output 

name     roll   adress              age   

John1    123    34,school street    12

'Not IN' condition is just reverse of the 'IN' condition. It just result value that does not match with supplied value .

Here is a example of 'Not IN' condition.

                   SELECT name,roll,adress, age

                      FROM student WHERE age NOT IN (12,14,17)

Output  

name     roll   adress              age   

John2    124    35,school street    13
John4    126    38,school street    15
John5    127    39,school street    16