This content has been marked as final. Show 3 replies
This is what Cartersian Product means.
'If two tables in a join query have no join condition, then Oracle returns their
Cartesian product. Oracle combines each row of one table with each row of the
other. A Cartesian product always generates many rows and is rarely useful. For
example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows.
Always include a join condition unless you specifically need a Cartesian product. If
a query joins three or more tables and you do not specify a join condition for a
specific pair, then the optimizer may choose a join order that avoids producing an
intermediate Cartesian product.'
I think join is also a sort of cartesian product only, but with a certain condition which we specify for the join.
eg. select a.col1, b.col1
from tab a, tab b;
This means Cartesian product of table with itself that is if there are 10 records then it returns 100 values
but if we give
select a.col1, b.col1
from tab a, tab b
then this becomes join and low number of rows will be returned i.e. we only refined the cartesian product for our condition.
I just want to confirm whether I am thinking on the right track or join means something else.
Actually it is the other way round. Actually it depends the way you see.. But normal it is stated this way.If you want to get data from two tables depending on one condition you use join. If you don't give a condition for joining it displays the cartesian product of the two tables.
If the condition for joining the table is equality sign then it is called equi join else if it is > or < then non-equi join.
Similary you have outer join, self join(joining table with itself based on a condition), inner join ,etc.
To learn more about joins take a look at the following link