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.
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.
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?
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.
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.