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

Pages

Sunday, 16 July 2017

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                      


Share this article :

0 comments:

Post a Comment