Results 1 to 3 of 3

Thread: Outer Joins

  1. #1
    Join Date
    May 2013
    Posts
    17

    Unanswered: Outer Joins

    Hello,

    i am trying to convert the code in oracle to postgres. i am new to postgres however, am aware that + sign is not supported unlike oracle. So could i get help in rewriting the below query in oracle into postgres please:

    SELECT t1.col1 , t2.col2, t3.col3, t4.col4
    FROM t1, t2, t3, t4
    WHERE t4.x1 = t1.a1(+)
    AND t4.x2 = t2.x2
    AND t4.x3 = t2.x3
    AND t4.x4 = t2.x4
    AND t4.x5 = t2.x5

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by zubi6050 View Post
    Hello,

    i am trying to convert the code in oracle to postgres. i am new to postgres however, am aware that + sign is not supported unlike oracle. So could i get help in rewriting the below query in oracle into postgres please:
    The (+) is deprecated in Oracle and should not be used. Oracle recommends to use explicit JOIN syntax (at least for outer joins - but I highly recommend to always use them).

    Something like this is probably what you are looking for:

    Code:
    SELECT t1.col1 , 
           t2.col2, 
           t3.col3, 
           t4.col4
    FROM t1 
      JOIN t2 ON t4.x2 = t2.x2 AND t4.x4 = t2.x4 AND t4.x5 = t2.x5
      JOIN t3 ON t4.x3 = t2.x3
      LEFT JOIN t4 ON t4.x1 = t1.a1
    It's been ages since I used Oracle's (+) operator so it might be that you want a RIGHT JOIN instead of a LEFT JOIN.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    May 2013
    Posts
    17
    Thank you Shammat.

    I got an idea from the query that you wrote, however, I think if i modify the query to add another condition for table t3 like:

    SELECT t1.col1 , t2.col2, t3.col3, t4.col4
    FROM t1, t2, t3, t4
    WHERE t4.x1 = t1.a1(+)
    AND t4.x2 = t2.x2
    AND t4.x3 = t2.x3
    AND t4.x4 = t2.x4
    AND t4.x5 = t2.x5
    AND t3.x6 = 'ABC';

    Then , if i rewrite it in postgresql, it should be something like:

    SELECT t1.col1 ,
    t2.col2,
    t3.col3,
    t4.col4
    FROM t1
    RIGHT JOIN t4 ON t4.x1 = t1.a1
    JOIN t2 ON t4.x2 = t2.x2 AND t4.x3 = t2.x3 AND t4.x4 = t2.x4 AND t4.x5 = t2.x5
    JOIN t3 ON t3.x6 ='ABC'

    Is this correct, please let me know

    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •