3 Replies Latest reply: May 23, 2003 3:21 AM by amajety RSS

    What is Cartesian product in Oracle?

      "Cartesian product" often appear in the Oracle book, what is it?
        • 1. Re: What is Cartesian product in Oracle?
          Hi Jin,
          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.'
          • 2. Re: What is Cartesian product in Oracle?
            Hi Anupma,
            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
            where a.col1=b.col1;
            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.

            • 3. Re: What is Cartesian product in Oracle?
              Hi Ashish,
              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