1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

no kartesian prooduct???

Discussion in 'SQL PL/SQL' started by monkey, Jun 9, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    o to op : n: 1

    But, I am not getting the whole Kartesian product: (full joint)

    Code (SQL):
    SELECT *
    FROM o, op
    WHERE o.id_o=op.id_op
    Anyone sees the mistake?
    many thanks in a dvance!!!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    See to links : JOINS
    Cartesian_join

    1)
    Full outer join fro example :

    Code (SQL):

    SELECT
     last_name,
     department_name,
    FROM
    employees e
    FULL OUTER JOIN departments d ON e.department_id =  d.department_id;
     

    2)

    Cartesian :

    For example....


    Code (SQL):

    WITH
    tab1 AS
    (SELECT level id , chr(ascii('A')+level) ch FROM dual
     CONNECT BY level <=7
     )
     ,
     tab2 AS
    (SELECT  chr(ascii('Z') - level) ch FROM dual
     CONNECT BY level <=7
     )

    SELECT  * FROM tab1 ,tab2;

     

    OR

    Code (SQL):

    WITH
    tab1 AS
    (SELECT level id , chr(ascii('A')+level) ch FROM dual
     CONNECT BY level <=7
     )
     ,
     tab2 AS
    (SELECT  chr(ascii('Z') - level) ch FROM dual
     CONNECT BY level <=7
     )

    SELECT  * FROM tab1
    CROSS JOIN tab2;
     
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The mistake is putting in a join condition between the two tables. By definition, you get a Cartesian product when there is no join condition between two tables. I'm not really sure what the preceding example is supposed to be indicating. The following example shows a Cartesian product between two tables.

    This sets up the two tables to generate the product:
    Code (Text):
    CREATE TABLE tab1 (let_col VARCHAR2(1));
    table TAB1 created.

    CREATE TABLE tab2 (num_col NUMBER);
    table TAB2 created.

    INSERT INTO tab1 VALUES ('a');
    1 rows inserted.
    INSERT INTO tab1 VALUES ('b');
    1 rows inserted.
    INSERT INTO tab1 VALUES ('c');
    1 rows inserted.

    INSERT INTO tab2 VALUES (1);
    1 rows inserted.
    INSERT INTO tab2 VALUES (2);
    1 rows inserted.
    INSERT INTO tab2 VALUES (3);
    1 rows inserted.

    This query produces a Cartesian product.
    Code (Text):
    SELECT tab1.let_col, tab2.num_col
    FROM   tab1,
           tab2;

    LET_COL    NUM_COL
    ------- ----------
    a                1
    a                2
    a                3
    b                1
    b                2
    b                3
    c                1
    c                2
    c                3

     9 rows selected
     
    Using ANSI SQL, the CROSS JOIN operator will generate the same results as the above query.
    Code (Text):
    SELECT tab1.let_col, tab2.num_col
    FROM   tab1
           CROSS JOIN tab2;
     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I rarely have use for a CROSS JOIN; I do occasionally find use for a FULL OUTER JOIN, which is different, as it won't generate what I consider 'useless' records (data 'matched' with non-matching records).