19

Possible Duplicate:
Explicit vs implicit SQL joins

I want to know the difference in performance of

select * from A,B,C where A.x = B.y and B.y = C.z

and

select * from A INNER JOIN B on A.x = B.y INNER JOIN C on B.y = C.z

Basically i want to know if inner join performs better than cartesian product? Also, in inner join is cartesian product carried out internally?

Community
  • 1
  • 1
nishantv
  • 643
  • 4
  • 9
  • 27
  • thanks guys for sharing the available thread. I dont know how didn't I find it. So basically both are same performance wise however INNER JOIN is more clear representation. – nishantv Feb 01 '13 at 13:21
  • 1
    Just a note: Neither of these are a cartesian product (a.k.a. cross join). A cartesian product is a join that has no join criteria, as in `select * from A,B` or `select * from A inner join B`. The result of this is a set that has _cardinality(A) * cardinality(B)_ rows. – Ian Bjorhovde Feb 04 '13 at 17:17
  • @IanBjorhovde I mentioned cartesian product as the conditions would be applicable only after the cartesian product. With inner join i was not sure if the case is similar. – nishantv Feb 05 '13 at 14:19
  • 1
    It doesn't matter whether you write the query in the first form or the second form, neither is a cartesian product. The DB2 optimizer would never perform a cartesian product and then filter the result to meet the conditions. I would venture a guess that this would apply to nearly any DBMS capable of performing a join. – Ian Bjorhovde Feb 07 '13 at 20:57

1 Answers1

2

First of All these two Operations are for Two different purposes , While Cartesian Product provides you a result made by joining each row from one table to each row in another table. while An inner join (sometimes called a simple join ) is a join of two or more tables that returns only those rows that satisfy the join condition.
Now coming to what You have Written here :
In case of Cartesian product First A table comprising of A,B,C is created and after that on the basis of what ever condition is given,we Get result. But as you see it's heavy process.
On the other hand Inner join only chooses those result which are really fulfilling the given condition .Hence it's a better solution for achieving end results.
First one is abuse of SQL language.

  • does this mean the second query will always take less time? Also, for inner join to choose the records it has to compare both tables. Doesnt this compare operation will be on one to one basis. just like cartesian product is formed? – nishantv Feb 01 '13 at 12:58
  • 1
    @nishantv take it like this: While in Cartesian product ,a bigger table is made first and then smaller table is created by choosing appropriate rows. Kinda Heavy Process. While in Inner Join which is optimized query inherently for this type of process ,will only choose those row from both of the tables ,which fulfills the criteria. And Yes Second one will always consume less "Resources". –  Feb 04 '13 at 03:56
  • in the thread mentioned above the discussion concludes with the fact that both queries are similar. I think I have to wait for an opportunity to try the queries on a huge db. Have you ever verified it practically? – nishantv Feb 05 '13 at 14:21
  • 17
    Just a clafirication for unaware readers. This answer is wrong. In MySQL, SqlServer and PostgreSQL there is no performance difference between both options proposed in the question. Please, refer to the linked duplicate for more details. – pacha Jul 29 '15 at 18:33