SQL IN Operator

SQL IN Operator use to pick the values with the given list.

It will Supports with all types of data.

SQL IN Syntax

           SELECT column1,column2..

                  FROM table_name  

                WHERE column_name IN (value1, value2, ...);

Example

The following statement returns values where empno having 101, 103, 105, 107, 109

SELECT *

  FROM emp     

 WHERE empno IN (101, 103, 105, 107, 109);

Update With SQL IN Operator

The Following statement update employee by adding 5000 where ename having ‘RAM’, ‘HARI’, ‘ANIL’

UPDATE emp

   SET sal = sal + 5000

 WHERE ename IN ('RAM', 'HARI', 'ANIL');

Delete with SQL IN Operator

The following statement delete employees where hire date is '10-jan-11','10-feb-11','10-mar-11'

DELETE FROM emp

      WHERE hire_date in ('10-jan-11','10-feb-11','10-mar-11');

SQL IN Subquery

We can use Subqueries with IN Operator multiple ways

Syntax for SQL IN Subquery

SELECT column1, column2 ..

  FROM table_name

 WHERE column_name IN (SELECT column_name FROM table_name);

Example

The following statement will returns employee details where deptno equals with dept table

 SELECT *

  FROM emp

 WHERE deptno IN (SELECT deptno FROM dept);

SQL NOT IN Operator 

SQL NOT IN Operator use to pick the values with the not given list.

It will Supports with all types of data.

SQL NOT IN Syntax

           SELECT column1,column2..

                  FROM table_name  

                WHERE column_name NOT IN (value1, value2, ...);

Example 

The following statement returns values where empno having 101, 102, 103, 105, 109

SELECT *

  FROM emp

 WHERE empno NOT IN (101, 102, 103, 105, 109);

Update With SQL NOT IN Operator

 

The Following statement update employee by adding 5000 where ename not equals  ‘RAM’, ‘HARI’, ‘ANIL’

UPDATE emp

   SET sal = sal + 5000

 WHERE ename NOT IN ('RAM', 'HARI', 'ANIL');

You May Like:                                         

                           SQL Like Operator

                            SQL Between Operator

                            SQL is NULL AND is NOT NULL