Question on anatomy of a query
in [Programming SQL]
|
Prev: Top 2 from count
Next: CREATE BLANK ROWS BETWEEN DATA
From: tshad on 11 Aug 2010 20:01 "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9DD1ED1996428Yazorman(a)127.0.0.1... > tshad (tfs(a)dslextreme.com) writes: >> Why? >> >> Why wouldn't it just read both tables and take the rows that match the ON >> filter? > > But how it read them? How would it know which fits together? Logically the > only way to do this is to create all combinations and then filter. > Makes sense. Thanks, Tom > Oh, in practice the optimizer does it another way, but here is the matter > of finding a neutral description. > > But as Celko points out, the SQL standards are written in a very > formalistic > language, and they are defintely not trivial to understand. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: tshad on 11 Aug 2010 20:03 "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:696d9d0a-8610-4272-8013-bc885429afaf(a)x21g2000yqa.googlegroups.com... > When you have more of a background, you might want to read some ANSI/ > ISO Standards. They are dull, boring and complicated and in a strange > language we called "Standard-speak" when i was on the committee. One > of the technical terms is "effectively" which means that we are about > to describe a way to do something (like that CROSS JOIN thing) which > will have some effect. But any process that produces that same result > is just as good. > > I work out a full SELECT statement in SQL FOR SMARTIES as per the > formal process just to show how this could be done. I'll have to check that out when I get home. Thanks, Tom
From: tshad on 12 Aug 2010 02:25 And what about the question of the JOINS: I agree that that is what happens which is why it is confusing when thinking about how joins work. I have always been told that you do the joins left to right and that each join becomes the left condition of the next join. For example, Select * From tableA a join tableB b on a.id = b.id join tablec c on b.id = c.id join tabled d on c.id = d.id where something. In this case, a is joined with b, b is joined with the result of a and b, d is joined with the results of a and b and c. If the optimizer changed the order this is done then you could possibly have a different result, especially if dealing with left joins or there is multiple criteria in one of the joins. Thanks, To "tshad" <tfs(a)dslextreme.com> wrote in message news:%23Yn7AGbOLHA.5700(a)TK2MSFTNGP04.phx.gbl... > > "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message > news:Xns9DD1ED1996428Yazorman(a)127.0.0.1... >> tshad (tfs(a)dslextreme.com) writes: >>> Why? >>> >>> Why wouldn't it just read both tables and take the rows that match the >>> ON >>> filter? >> >> But how it read them? How would it know which fits together? Logically >> the >> only way to do this is to create all combinations and then filter. >> > > Makes sense. > > Thanks, > > Tom > >> Oh, in practice the optimizer does it another way, but here is the matter >> of finding a neutral description. >> >> But as Celko points out, the SQL standards are written in a very >> formalistic >> language, and they are defintely not trivial to understand. >> >> -- >> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se >> >> Links for SQL Server Books Online: >> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >> SQL 2000: >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> > >
From: Erland Sommarskog on 12 Aug 2010 03:24 tshad (tfs(a)dslextreme.com) writes: > For example, > > Select * > From tableA a > join tableB b on a.id = b.id > join tablec c on b.id = c.id > join tabled d on c.id = d.id > where something. > > In this case, a is joined with b, b is joined with the result of a and > b, d is joined with the results of a and b and c. > > If the optimizer changed the order this is done then you could possibly > have a different result, especially if dealing with left joins or there is > multiple criteria in one of the joins. No. An inner join is just like addition and multiplication a commutative operator. Meaning that "A JOIN B" <=> "B JOIN A". With left and right joins this is obviously not the case. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: tshad on 12 Aug 2010 11:17
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9DD25FAE1B0D7Yazorman(a)127.0.0.1... > tshad (tfs(a)dslextreme.com) writes: >> For example, >> >> Select * >> From tableA a >> join tableB b on a.id = b.id >> join tablec c on b.id = c.id >> join tabled d on c.id = d.id >> where something. >> >> In this case, a is joined with b, b is joined with the result of a and >> b, d is joined with the results of a and b and c. >> >> If the optimizer changed the order this is done then you could possibly >> have a different result, especially if dealing with left joins or there >> is >> multiple criteria in one of the joins. > > No. An inner join is just like addition and multiplication a commutative > operator. Meaning that "A JOIN B" <=> "B JOIN A". Right but would: A JOIN B B JOIN C C JOIN D be the same thing as A JOIN B C JOIN D B JOIN C If so, the 3rd join IS NOT joining against the previous result set. In otherwords, would the first example be: (A JOIN B B JOIN C) a1 D JOIN a1 Where D is being joined with the result of the 1st 2 joins so only joining with the rows in C that matched with the rows in B that matched with the rows in A. This could give less rows that the original set than if D were JOIN'd with the whole table of C. And if result sets were joined with each other then the 2nd example would have the results of A JOIN B join'd with the result set of C JOIN B. Of course, if previous result sets are not used that this is all mute and it doesn't matter what the order is. Thanks, Tom > > With left and right joins this is obviously not the case. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |