Can someone explain why this is not an error?

I accidentally did something similar to this, and I can't figure out why it
doesn't give an error.
Use Pubs
Select * From Authors
Where Au_LName In (Select Au_LName From Publishers)
Of course, "Select Au_LName From Publishers" by itself gives an error
message since Publishers doesn't have a field called AULName.
What's up? An error message would have helped here.
For a slightly different case, if the two tables have a field name in
common, and you use thet field in the inner Select, but don't qualify the
field name in the inner Select, what happens?
Thanks.
David WalkerDavid,
See the following thread:
http://groups.google.com/group/micr...6c9a254304c7629
HTH
Jerry
"DWalker" <none@.none.com> wrote in message
news:%239LoUZSxFHA.3720@.TK2MSFTNGP11.phx.gbl...
>I accidentally did something similar to this, and I can't figure out why it
> doesn't give an error.
> Use Pubs
> Select * From Authors
> Where Au_LName In (Select Au_LName From Publishers)
> Of course, "Select Au_LName From Publishers" by itself gives an error
> message since Publishers doesn't have a field called AULName.
> What's up? An error message would have helped here.
> For a slightly different case, if the two tables have a field name in
> common, and you use thet field in the inner Select, but don't qualify the
> field name in the inner Select, what happens?
> Thanks.
> David Walker|||I think I know what happened here (someone more knowledgeable can correct me
if I am wrong)...
Since there is no Au_LName column in the Publishers table, SQL Server goes
looking for any other place in your statement that it can find a table with
a column named Au_LName. Since the Authors column is 'in scope' within the
subquery, the statement you posted is functionally equivalent to this:
Select * From Authors
Where Au_LName In (Select Authors.Au_LName From Publishers)
This in turn is functionally equivalent to:
--note the cartesian product in the subquery
Select * From Authors
Where Au_LName In (Select Authors.Au_LName From Authors, Publishers)
If the Authors table was not 'in scope' in the subquery, then you could not
write correlated subqueries, since the outer table(s) would not be
accessible from inside the subquery. So everything worked as it should, but
it ends up looking really strange if you are not expecting this to happen.
"DWalker" <none@.none.com> wrote in message
news:%239LoUZSxFHA.3720@.TK2MSFTNGP11.phx.gbl...
>I accidentally did something similar to this, and I can't figure out why it
> doesn't give an error.
> Use Pubs
> Select * From Authors
> Where Au_LName In (Select Au_LName From Publishers)
> Of course, "Select Au_LName From Publishers" by itself gives an error
> message since Publishers doesn't have a field called AULName.
> What's up? An error message would have helped here.
> For a slightly different case, if the two tables have a field name in
> common, and you use thet field in the inner Select, but don't qualify the
> field name in the inner Select, what happens?
> Thanks.
> David Walker|||Correction: that should be "Authors table", not "Authors column".
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:uL64ZQTxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>I think I know what happened here (someone more knowledgeable can correct
>me if I am wrong)...
> Since there is no Au_LName column in the Publishers table, SQL Server goes
> looking for any other place in your statement that it can find a table
> with a column named Au_LName. Since the Authors column is 'in scope'
> within the subquery, the statement you posted is functionally equivalent
> to this:
> Select * From Authors
> Where Au_LName In (Select Authors.Au_LName From Publishers)
> This in turn is functionally equivalent to:
> --note the cartesian product in the subquery
> Select * From Authors
> Where Au_LName In (Select Authors.Au_LName From Authors, Publishers)
> If the Authors table was not 'in scope' in the subquery, then you could
> not write correlated subqueries, since the outer table(s) would not be
> accessible from inside the subquery. So everything worked as it should,
> but it ends up looking really strange if you are not expecting this to
> happen.
> "DWalker" <none@.none.com> wrote in message
> news:%239LoUZSxFHA.3720@.TK2MSFTNGP11.phx.gbl...
>|||OK, I double checked the execution plans, and I can plainly see that my
second statement is *not* equivalent - just ignore that. However, I appear
to have gotten the first part right, so I am batting .500!
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:%23$%233fVTxFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Correction: that should be "Authors table", not "Authors column".
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:uL64ZQTxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>|||Jeremy,
Batting .500...nice...you're leading both the National and the America
leagues. Would you mind batting for the Red Sox this wend against the
Yankees? ;-)
Your theory makes sense. What really looks weird is when you replace the
Publisher's table with one of the system tables.
HTH
Jerry
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:OSkXksTxFHA.2212@.TK2MSFTNGP15.phx.gbl...
> OK, I double checked the execution plans, and I can plainly see that my
> second statement is *not* equivalent - just ignore that. However, I appear
> to have gotten the first part right, so I am batting .500!
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:%23$%233fVTxFHA.3556@.TK2MSFTNGP12.phx.gbl...
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in
news:e1hEnxTxFHA.3900@.TK2MSFTNGP10.phx.gbl:

> Jeremy,
> Batting .500...nice...you're leading both the National and the America
> leagues. Would you mind batting for the Red Sox this wend against
> the Yankees? ;-)
> Your theory makes sense. What really looks weird is when you replace
> the Publisher's table with one of the system tables.
> HTH
> Jerry
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:OSkXksTxFHA.2212@.TK2MSFTNGP15.phx.gbl...
>
Baseball aside (go Braves!), yes it perhaps looks like a correlated
subquery, but it's really strange if you're not expecting it. But
correlated subqueries at least require you to say which field(s) you're
joining the two tables on.
In that older thread (which I started although it was nine months ago
and I made the same mistake again), Andrew Kelly said "If the column
name in the subselect is not part of that table it looks to the parent
select and if it finds it there it thinks all is well". But if it's
joining the inner select table with the table in the parent select,
you'd have to tell it which field to join on, I would think, unless it
assumes that any field that happened to be named the same is the join
field (or it's a Cartesian product). If you're not joining, then you're
just ignoring the table in the inner select?
While this might be per ANSI standards, I don't see it mentioned in BOL
anywhere.
This example from that thread:
SELECT *
FROM TblA
WHERE TblA.Col =
(SELECT TblA.ColB - TblB.ColC
FROM TblB)
is weird. How can you do the subtraction without knowing an ID column?
Or is it also a Cartesian product?
David|||On Fri, 30 Sep 2005 07:25:23 -0700, DWalker wrote:

>Baseball aside (go Braves!), yes it perhaps looks like a correlated
>subquery, but it's really strange if you're not expecting it. But
>correlated subqueries at least require you to say which field(s) you're
>joining the two tables on.
Hi David,
They usually do, but it's not a syntactical requirement. To understand
how SQL Server finds which column to use if you don't explicitly qualify
it, check out the following example:
SELECT ...
FROM Tab1
INNER JOIN Tab2
ON ...
WHERE ... = (SELECT ...
FROM Tab3
INNER JOIN Tab4
ON ...
WHERE ...)
Let's say that an unqualified column (TheCol) is found in the subquery.
These steps are taken:
1. Look in both Tab3 and Tab4 for a column with the name TheCol.
2. If a column with that name is found in both tables: raise error.
3. If a column with that name is found in one of the tables: use it.
4. If a column with that name is found in neither table, continue to
check Tab1 and Tab2 for a column named TheCol.
5. If a column with that name is found in both tables: raise error.
6. If a column with that name is found in one of the tables: use it.
7. If a column with that name is found in neither table: raise error.
On the other hand, if a reference to TheCol is found outside of the
subquery, only Tab1 and Tab2 are checked, since it's outside of the
scope where Tab3 and Tab4 are valid.
Of course, it's much better to get into the habit of qualifying all
column names whenever you use more than one table in your query!

>This example from that thread:
>SELECT *
>FROM TblA
>WHERE TblA.Col =
> (SELECT TblA.ColB - TblB.ColC
> FROM TblB)
>is weird. How can you do the subtraction without knowing an ID column?
It will only work if TblB has one or zero rows. Otherwise, you'll get an
error ("subquery returned more than one row").
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)