Sub-Select Wierdness
-
This is very strange. The following statement executes without error:
select distinct code
from Table1
where code is not null
and code <> ''
and code in (select code from table2)
The problem is, the code column does not exist in table2. I would expect an error to be generated by sql, but it returns a result set.
If I alias the table in the sub-select (select a.code from table2 a) the statement will fail and return a column does not exist error.
Has anybody ever run across this before? I wasn't even sure how to search for this!
Thanks in advance.
P.S. -- This is sql server 2000 standard edition running sp3
-
Your statement is identicel to
select distinct code
from Table1
where code is not null
and code <> ''
and code in (select Table1.code from table2)
You may reference columns from Table1 in the subselect of Table2. All columns, of both tables are avaialble in the subselect, since Table2 does not have a
column, SQl use the value from Table1.code. In effect, the subselect would always be true.Above is my opinion only. Not ness. a fact.
Once you understand the BITs, all the pieces come together
-
ThomasH, you are correct in that the subselect will evaluate to
select code from table1, table2.
That query returns no rows. Thus, including it in the main query will result in a recordset with no rows.
-
I see what you are saying, but the query does returns 7 rows. That is what makes it so strange. It's like sql server is not even evaulating the sub select.
-
quote:
I see what you are saying, but the query does returns 7 rows. That is what makes it so strange. It's like sql server is not even evaulating the sub select.I believe you didn't read Thomas post! Your third condition is always true and that's why you get the rows
* Noel -
Let me clarify.
Since the subselect evaluates as select code from table1, table2, you get a cartesian product. In other words, you get all of the values from table1 repeated for each row of table2. So if table2 has no rows, you get no rows. if table1 has 7 rows and table2 has 2 rows, you get 14 rows. And so on.
But you're using the subselect in an "in" clause, thuse repeats don't matter. So as long as table2 has rows in it, the subselect evaluates true. If table2 has no rows, it evaluates false.
-
Thanks for all your input on this. So, what you are saying is that any column from table1 placed in the sub select will return the same results since the sub select will be evaluated as true. I'll test this on Monday with the following:
select distinct code
from Table1
where code is not null
and code <> ''
and code in (select (different Table1 column then code) from table2)
I'll let you know how it works out.
-
Not quite what was meant. The original subquery evaluates to a (possibly repeated) set of all the code values from table1. Thus the condition "IN" always evaluates to true. The subquery does not itself evaluate to true. The change you propose would almost certainly fail as it would check for code values that are "IN" a set of another columns values.
quote:
So, what you are saying is that any column from table1 placed in the sub select will return the same results since the sub select will be evaluated as true. I'll test this on Monday with the following:select distinct code
from Table1
where code is not null
and code <> ''
and code in (select (different Table1 column then code) from table2)
-
What RonClarke said.
When I said that the subquery returns all the values in table1, I meant all of the values for the column specified in the subquery.
-
OK...The light bulb has turned on. Thanks for your help and have a great week!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply