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 :

4 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
  2. Hey,


    I learnt so much in such little time about SQL IN Operator. Even a toddler could become smart reading of your amazing articles.
    I have requirement like

    select COL1,Col2 from A
    minus
    Select COL1,COL2 from B;

    both tables have 50+ millions record so I want to automate a job automate a job which will execute this query for 1millions records for one time and then again 1 millions for second time...like loop and store output in record type.


    But nice Article Mate! Great Information! Keep up the good work!


    Kind Regards,
    Nithya

    ReplyDelete
  3. Hello There,

    I genuinely do look forward for the time where you post some new write ups. Your blog make me feel so educated! Continue soaring and writing please.

    I have to collect the past execution plan of specific statement(which runs somewhere around midnight everyday).
    Then I will have to compare the last execution plans of the 3 same statement . I run AWR stats collection every hour and have history upto 30 days.

    I am trying to run the following but it is not showing me even the last 3 executions of specific statement ( identified by .... like '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'.......) I have to collect the past execution plan of specific statement(which runs somewhere around midnight everyday).
    Then I will have to compare the last execution plans of the 3 same statement . I run AWR stats collection every hour and have history upto 30 days.

    I am trying to run the following but it is not showing me even the last 3 executions of specific statement ( identified by .... like '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'.......)

    Once again thanks for your tutorial.

    Kind Regards,
    Preethi.

    ReplyDelete
  4. Greetings Mate,


    This is indeed great! But I think perhaps you are generally referring #topic which is getting unsustainable.


    I need some help from you experts on this stored procedure..

    There are 4-5 batch procedures that will call this stored procedure. The batch will call the stored procedure with 'I' parameter in the beginning of the batch process. The batch will call the stored procedure with 'O' parameter at the end of the batch process. The stored procedure should write the details into the auditing table.






    But great job man, do keep posted with the new updates.

    morgan

    ReplyDelete