2 Replies Latest reply: Dec 30, 2012 3:07 AM by Mohamed Houri RSS

    NOT IN AND NOT EXIST

    Oracle Maniac
      Hi Folks ,


      Was going through Tom's explanation for IN AND EXISTS .
      http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:953229842074.


      DOUBTS :

      NOT IN and NOT EXISTS should apparently behave similar to IN AND EXISTS. I mean , in case of NOT IN - The
      sub-query is transformed to a view with distinct values and a join . but an equi join wouldn't be
      needed here .So how does it work ?

      Also for NOT EXISTS ,suppose the inner table is big enough and indexed on the column .Since we
      arent matching any values, i suppose the index wont be used. so how does NOT EXISTS work ?
        • 1. Re: NOT IN AND NOT EXIST
          Frank Kulash
          Hi,
          Rahul  K wrote:
          Hi Folks ,


          Was going through Tom's explanation for IN AND EXISTS .
          http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:953229842074.
          It's not very clear what you're asking. It would help if you quoted particular parts from theat page, and menitioned roughly where on the page they appear.
          DOUBTS :

          NOT IN and NOT EXISTS should apparently behave similar to IN AND EXISTS. I mean , in case of NOT IN - The
          sub-query is transformed to a view with distinct values and a join . but an equi join wouldn't be
          needed here .So how does it work ?
          Why wouldn't an equi-join be needed? If a join is being done, I think it would have to be an equi-join. What kind of join would you use?
          Also for NOT EXISTS ,suppose the inner table is big enough and indexed on the column .Since we
          arent matching any values, i suppose the index wont be used. so how does NOT EXISTS work ?
          Whether it's EXISTS or NOT EXISTS, you have to see whether a match exists or not. An index would be helpful either way.

          Say you're working for a movie rental business, and you have an index of which title are available. Your boss gives you a list of titles, and asks you to find which ones your business has. You don't know how your boss is going to use the results you produce: perhaps he's going to feature the ones you have in an advertisement, or perhaps he's going to buy the ones you don't have. For your job, you don't need to know how ypour boss is going to use the results; you're going to look at the index either way.
          • 2. Re: NOT IN AND NOT EXIST
            Mohamed Houri
            NOT IN and NOT EXISTS should apparently behave similar to IN AND EXISTS
            That would be simple in the absence of the inevitably NULL values.

            http://jonathanlewis.wordpress.com/2007/02/25/not-in/


            Think about what Jonathan Lewis wrote : *“NOT IN” is not the opposite of “IN”*

            Best Regards
            Mohamed Houri
            www.hourim.wordpress.com