12 Replies Latest reply: Aug 1, 2012 5:37 AM by Venkadesh Raja RSS

    sql query

    CoolBuddy
      Need help in query using exists

      i have written a query

      select department_id from departments where department_id not in (select distinct department_Id from employees)

      am getting correct o/p but i want this o/p using exists

      i have tried this but blank o/p

      select department_id from departments where not exists (select distinct department_Id from employees)


      what is the reason....
        • 1. Re: sql query
          Frank Kulash
          Hi

          EXISTS sub-queries almost always need to be correlated, like this:
          SELECT  department_id 
          FROM     departments
          WHERE      NOT EXISTS (
                         SELECT  0
                         FROM     employees
                         WHERE     department_id = departments.department_id
                       )
          ;
          • 2. Re: sql query
            CoolBuddy
            thankq Frank Kulash its working but whats the prob in my query???
            • 3. Re: sql query
              Venkadesh Raja
              try this
              select department_id
               from departments a
              where not exists (select 1 from employees b where a.department_id=b.department_id)
              • 4. Re: sql query
                Venkadesh Raja
                CoolBuddy wrote:
                thankq Frank Kulash its working but whats the prob in my query???
                join condition missing
                • 5. Re: sql query
                  Khayyam
                  When you using EXIST/NOT EXIST statement you should use correlated subquerry:
                  select department_id from departments where not exists 
                  (select 1 from employees where department_Id=departments.department_Id)
                  In your querry NOT EXISTS part allways return value so Oracle think data allways EXISTS.
                  • 6. Re: sql query
                    CoolBuddy
                    we can achieve the same using in and not in na which is better approach and which is less cost effective...
                    • 7. Re: sql query
                      Purvesh K
                      CoolBuddy wrote:
                      we can achieve the same using in and not in na which is better approach and which is less cost effective...
                      Incorrect.

                      Exists will check only for presence of records in sub-query.
                      In/Not IN will check for each record in the sub-query.

                      Thus, Exist clause will provide a minor (at least) performance advantage.
                      • 8. Re: sql query
                        Venkadesh Raja
                        CoolBuddy wrote:
                        we can achieve the same using in and not in na which is better approach and which is less cost effective...
                        Depends. But i prefer NOT EXISTS
                        • 9. Re: sql query
                          Khayyam
                          Didnt you get an answer of your question ? Or there is something else you interested in ?
                          • 10. Re: sql query
                            Khayyam
                            CoolBuddy wrote:
                            we can achieve the same using in and not in na which is better approach and which is less cost effective...
                            Not allways...
                            • 11. Re: sql query
                              Frank Kulash
                              Hi,
                              CoolBuddy wrote:
                              thankq Frank Kulash its working but whats the prob in my query???
                              The complete query is:
                              select department_id from departments where not exists (select distinct department_Id from employees)
                              Look at just the sub-query:
                              (select distinct department_Id from employees)
                              If there are any rows in the employees table, then this sub-query will return some rows.
                              Remember what EXISTS means. EXISTS returns TRUE if the sub-query returns any rows, and FALSE if the sub-query does not return any rows. In this case, the sub-query (I assume) does return some rows, so EXISTS is TRUE, and NOT EXISTS is FALSE.

                              Once again, EXISTS returns TRUE or FALSE. If the EXISTS sub-query is not correlated to a super-query, then it will always produce the same results. If it's TRUE once, then it will be TRUE all the time, and if it's FALSE once, it will be FALSE all the time. It's very rare that you want a condition that's always the same. If the EXISTS sub-query is correalted, then what it returns depends on something in the super-query, so it may return TRUE some times and FALSE other times.
                              • 12. Re: sql query
                                Venkadesh Raja
                                CoolBuddy wrote:
                                we can achieve the same using in and not in na which is better approach and which is less cost effective...
                                See the karthick excellent explanation

                                Re: Frequent questions: NOT IN vs  NOT EXISTS