Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    Unanswered: not exists & not in, give different result

    hi

    I got different results from not exists query and not in query. any body have an idea why ?

    Query-1 (Give rows)
    ======
    select * from cm_out_master a
    where not exists (select 1 from cm_out_history z where a.o_ccnb_cons = z.oh_ccnb_consumer)

    Query-2 (No any row resulted)
    ======
    select * from cm_out_master a
    where o_ccnb_cons not in (select oh_ccnb_consumer from cm_out_history)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nooralig
    any body have an idea why ?
    the presence of a NULL value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    no null value exist in that field

    Hi

    Thanks for your quick reply, but its not answer my question. In both table this field is primay key so no possible value exists in that field.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by nooralig
    Thanks for your quick reply, but its not answer my question. In both table this field is primay key so no possible value exists in that field.
    r937 was referring to the column oh_ccnb_consumer.
    Check if you have null values in that column.

    The solution to the problem is
    Code:
    SELECT * 
    FROM cm_out_master a
    WHERE o_ccnb_cons NOT IN (SELECT oh_ccnb_consumer 
                              FROM cm_out_history 
                              WHERE oh_ccnb_consumer IS NOT NULL)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •