Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Sunday, 16 July 2017

SQL Where IN Array



SQL IN Operator array use to pick the values with the given list.
It will Supports with all types of data.

SQL IN Array 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 Array 

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 Array 


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 Operator with Subquery Array 


We can use Subqueries with IN Operator multiple ways

Syntax for SQL IN Subquery Array 


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 Array 


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 Array 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 Array 


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');
Share this article :

0 comments:

Post a Comment