Why Inner join is better than equi join
in [Programming SQL]
|
From: --CELKO-- on 14 Jun 2010 12:37 >>Vinod, are you talking about the ANSI-style syntax (from t1 join t2 on <join_criteria>) vs. the old-style non-ANSI syntax (from t1, t2 where <join_criteria mixed with filter_criteria>)? << Another mis-statement on your part. The binary infixed and original n- ary notations are both ANSI/ISO Standard. From teaching classes and being on the SQL Committee when the binary infixed notation was approved, the main difference is that younger programmers who do not think in sets like binary infixed notation because it is familiar to them and can be read sequentially. Older SQL programmers or those with a mathematical background like the original notation. In part, because it is familiar to them. But also because they think in multiple sets at a time and not in sequential steps. Think about how clean a Star Schema search condition looks, how easy it is re-arrange search conditions, how it caters to the Law of Proximity, etc. It is the difference in your mindset between using plus signs (+) and Big Sigma summation in your math.
From: Bob Barrows on 14 Jun 2010 13:05 --CELKO-- wrote: >>> Vinod, are you talking about the ANSI-style syntax (from t1 join t2 >>> on <join_criteria>) vs. the old-style non-ANSI syntax (from t1, t2 >>> where <join_criteria mixed with filter_criteria>)? << > > Another mis-statement on your part. The binary infixed and original n- > ary notations are both ANSI/ISO Standard. > Well, I stand corrected again -- HTH, Bob Barrows
From: Tony Rogerson on 14 Jun 2010 13:37 > Older SQL programmers or those with a mathematical background like the > original notation. In part, because it is familiar to them. But also > because they think in multiple sets at a time and not in sequential > steps. Think about how clean a Star Schema search condition looks, how > easy it is re-arrange search conditions, how it caters to the Law of > Proximity, etc. What a complete load of twoddle. It's nothing more than being too lazy to learn the infix. Also, the old style does not reflect relational theory very well, the FROM clause does not allow join predicates in the old style and forces you to use just one join type - Cartesian or Natural if the product supports it. You end up sticking all the join relations in the restriction clause (the WHERE) which is just wrong. The newer infix notation reflects better relational theory in that it separates the join criteria from the restriction criteria. Whilst I fully realise you will not agree with the above try and justify your reasoning against the relational model, supposedly on top is where SQL sits. --ROGGIE-- "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:eb90c05c-d48d-4a42-b452-05d752c229f6(a)b35g2000yqi.googlegroups.com... >>>Vinod, are you talking about the ANSI-style syntax (from t1 join t2 on >>><join_criteria>) vs. the old-style non-ANSI syntax (from t1, t2 where >>><join_criteria mixed with filter_criteria>)? << > > Another mis-statement on your part. The binary infixed and original n- > ary notations are both ANSI/ISO Standard. > > From teaching classes and being on the SQL Committee when the binary > infixed notation was approved, the main difference is that younger > programmers who do not think in sets like binary infixed notation > because it is familiar to them and can be read sequentially. > > Older SQL programmers or those with a mathematical background like the > original notation. In part, because it is familiar to them. But also > because they think in multiple sets at a time and not in sequential > steps. Think about how clean a Star Schema search condition looks, how > easy it is re-arrange search conditions, how it caters to the Law of > Proximity, etc. > > It is the difference in your mindset between using plus signs (+) and > Big Sigma summation in your math.
From: Tony Rogerson on 14 Jun 2010 13:40
Hi Vinod, You are actually saying two different things here, basically they aren't the same. Inner join is a set based operator that creates a relationship between two tables. The self join is taking two instances of the same table and using a join relationship type such as an inner join - but could also be an outer join or cross join. Why do you ask? Many thanks, Tony. "Deep" <vinodkus(a)gmail.com> wrote in message news:3199a096-ca70-4c1e-b70f-d411f37a35d3(a)n37g2000prc.googlegroups.com... > Dear friends, > Inner join and self join both are same. Can you tell me why we > prefer inner join? > > Thanks & Regards > Vinod kumar |