|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can someone explain why this is not an error?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 David,
See the following thread: http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/9c3344b76448b5cb/f6c9a254304c7629?lnk=st&q=Please+explain+why+this+Select+doesn%27t+fail&rnum=1&hl=en#f6c9a254304c7629 HTH Jerry Show quote "DWalker" <n***@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. Show quote "DWalker" <n***@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".
Show quote "Jeremy Williams" <jeremydw***@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" <n***@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 > > 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! Show quote "Jeremy Williams" <jeremydw***@netscape.net> wrote in message news:%23$%233fVTxFHA.3556@TK2MSFTNGP12.phx.gbl... > Correction: that should be "Authors table", not "Authors column". > > "Jeremy Williams" <jeremydw***@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" <n***@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 >> >> > > Jeremy,
Batting .500...nice...you're leading both the National and the America leagues. Would you mind batting for the Red Sox this weekend 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 Show quote "Jeremy Williams" <jeremydw***@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" <jeremydw***@netscape.net> wrote in message > news:%23$%233fVTxFHA.3556@TK2MSFTNGP12.phx.gbl... >> Correction: that should be "Authors table", not "Authors column". >> >> "Jeremy Williams" <jeremydw***@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" <n***@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 >>> >>> >> >> > >
Show quote
"Jerry Spivey" <jspi***@vestas-awt.com> wrote in Baseball aside (go Braves!), yes it perhaps looks like a correlated 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 weekend 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" <jeremydw***@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" <jeremydw***@netscape.net> wrote in message >> news:%23$%233fVTxFHA.3556@TK2MSFTNGP12.phx.gbl... >>> Correction: that should be "Authors table", not "Authors column". >>> >>> "Jeremy Williams" <jeremydw***@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" <n***@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 >>>> >>>> >>> >>> >> >> > 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 Hi David,>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. 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: It will only work if TblB has one or zero rows. Otherwise, you'll get an> >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? error ("subquery returned more than one row"). Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics