Results 1 to 14 of 14
  1. #1
    Join Date
    May 2003
    Posts
    26

    Unanswered: Strange results using IN

    I received some strange results after typing the wrong column in a subselect using IN. I've been puzzling over it a while now and can't quite figure out why it is doing what it is.
    Here's an example of what is happening.


    use northwind
    go

    select * from Customers where CustomerID in
    ( select CustomerID from Employees where 1 = 0 )


    Now, there is no customerid on the Employees table, so running the subquery alone will produce an error. However when the full query above is run, all rows in the customer table are returned.

    It was my understanding that the inner querry is evaluated first, and the results used by the outer query. I'm having a hard time seeing how this query is going anywhere.

    Any ideas?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think I remember a post about something similar... But that's IN for you, never use it!

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for what it is worth, your Northwind query produced zero rows on my machine.

    -PatP

  4. #4
    Join Date
    May 2003
    Posts
    26
    Ooops!! Sorry about that!!! Was supposed to be

    select * from Customers where CustomerID in
    ( select CustomerID from Employees )

    I was playing around with some different where clauses while trying to figure out what was going on and left the 'where 1 = 0' in accidently.

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    that's an easy one:

    the inner query knows about the presense of the outer query, while reverse is not true. so, when a reference is made to customerid the optimizer "knows" that you are referring to the field from customers table, not employees as you "really" think. got it?

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    and the following query will produce the same result, except for the order:

    select customers.* from customers
    where exists (select customerid from employees)

  7. #7
    Join Date
    May 2003
    Posts
    26
    Thanks, I see how the results are being returned now but am a bit puzzled as to the why. Is this intended to operate this way? It seems more than a little bit dangerous... if I type something wrong I prefer to be told I've typed it wrong so I can correct it rather than have the QA decide what do do for me.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ms_sql_dba
    and the following query will produce the same result, except for the order:

    select customers.* from customers
    where exists (select customerid from employees)
    Huh?

    Where's the correlation?

    And I'm at a loss to understand what your goal is......
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rdjabarov
    I think I remember a post about something similar... But that's IN for you, never use it!
    And huh again?

    Building SQL is all a matter of understanding your data...things evaluate differently.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Nicely done, Ms. DBA, nicely done!

    What is there not to understand? Inner query is well aware of the CustomerID field, even though you're selecting it from Employees! The optimizer does not care what your source table is. You want to select CustomerID, - fine, here it is, because it's in the outer query. To test the sanity, replace CustomerID with "anything" without quotes, and you'll get an error. What does that mean? It's not that the optimizer missed something, it's because it didn't! But the developer did, that's the key.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I bow to your disconnected ramblings...

    I am in, in a word, awe

    Oh, and do you think that that's what Donner was asking help for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh, and do you think that that's what Donner was asking help for?
    Hmm, I think so...
    Originally posted by Donner
    Thanks, I see how the results are being returned now but am a bit puzzled as to the why.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK then...next...makes a lot of sense...you ever do that?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What, ask questions? Yup

Posting Permissions

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