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!

Joins

Discussion in 'SQL PL/SQL' started by bnramesh8, Sep 4, 2014.

  1. bnramesh8

    bnramesh8 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    What is the alternative for FULL OUTER JOIN?
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    A full join, an inner join, no join, a cartesian product, a left outer join, a right outer join...you might as well as what is the alternative to blue.

    What's the real question?
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

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

    Additional links : Join

    Simple example :
    Code (SQL):

    CREATE TABLE T11
    AS
    SELECT  1 A , 2 B FROM dual UNION ALL
    SELECT  2   ,   3 FROM dual UNION ALL
    SELECT  3   ,   5 FROM dual;

    CREATE TABLE T22
    AS
    SELECT   7 X   , 2   Y  FROM dual UNION ALL
    SELECT  8   ,   4 FROM dual UNION ALL
    SELECT  9   ,   4 FROM dual;

    SELECT
    *
    FROM t11 t1
    FULL JOIN t22 t2 ON t2.x = t1.a
                        AND
                        t2.y = t1.b;


    SELECT *
    FROM T11 LEFT OUTER JOIN T22
        ON (T11.A > 9 AND T11.B = T22.Y);
     
     
  5. bnramesh8

    bnramesh8 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    My Query is i want to retrieve the full outer join result with out using full outer join keyword?
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    it is necessary to execute sequence of operations over tables which need to be joined : left join and right join and union all
     
  7. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    So instead of :

    from emp e right outer join dept d

    ...which is ANSI SQL

    use

    from emp e, dept d
    where e.deptno(+) = d.deptno

    or

    where e.deptno = d.deptno(+)


    ...depending on what you want to do. These latter entries are Oracle's specific Outer Join syntax.

    HTH
     
  8. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    That's like saying you want to insert rows into a table without using the INSERT command. It may be possible to do (i.e. you could use MERGE instead of an INSERT), but whether or not it is a valid solution for the problem you are trying to solve depends on what the real problem is.

    In your case... if the data you want to retrieve is that which you would normally use a FULL OUTER JOIN for, what is the specific reason that you do not want to use the standard syntax?