Performance of Inner Join vs Cartesian product

sql join db2
nishantv picture nishantv · Feb 1, 2013 · Viewed 19.5k times · Source

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?

Answer

user1909647 picture user1909647 · Feb 1, 2013

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.

Related questions

how to avoid duplicate on Joining two tables

Student Table SID Name 1 A 2 B 3 C Marks Table id mark subject 1 50 physics 2 40 biology 1 50 chemistry 3 30 mathematics SELECT distinct(std.id),std.name,m.mark, row_number() over() as rownum FROM student std JOIN marks m ON std.id=m.id …

Read More
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More
SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More