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 :

1 comments:

  1. Hi Dayakar,


    Nice to be visiting your blog again, it has been months for me. Well this article that i’ve been waited for so long.

    We have 2 TFS environments used by two different applications having different set of users.
    One is TFS 2015 & Another is TFS 2017, we need to merge both of them into one , i'e there will be now only one TFS 2017 which will be having both (TFS 2015 + TFS 2017).
    1st step would be to upgrade the TFS 2015 one to TFS 2017. Now after that we will have 2 separate TFS 2017 .
    Now how to merger them both into one TFS 2017.
    Consider both of them having SQL Server 2014 in the backend.


    But great job man, do keep posted with the

    new updates.



    Best Regards,
    Morgan

    ReplyDelete