While there are a number of Join types Oracle offers, most of them work as is in PostgreSQL except the Outer joins written using native outer join operator denoted as (+). If the Oracle query contains keyword like LEFT OUTER JOIN/ RIGHT OUTER JOIN then these work same way in PostgreSQL as well.

Let’s explore and understand how to convert native SQL joins to ANSI standard queries. Let’s take below example which joins two tables using outer join operator and convert the query to ANSI standard SQL query.

SELECT *
FROM TABLE1 T1, TABLE2 T2
WHERE T1.Col = T2.Col1 (+);

Before you plan refactor above query to ANSI standard, first find out which is left table and which one is right by looking at the join condition specified in the WHERE clause. If (+) operator is specified on the 1st table then it is RIGHT outer join. If (+) operator is specified on the 2nd table then it is RIGHT outer join. Keeping that in mind, in the above example (+) operator is specified at the second table which is “TABLE2 alias as T2”, hence, this is a LEFT JOIN.

The ANSI version of above query is as below:

SELECT *
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2  ON T1.Col = T2.Col1;

Explore more Oracle join queries and migrate to PostgreSQL

When multiple tables are specified in the FROM clause, then they are joined based on the order specified in FROM clause, not on the join conditions mentioned in WHERE clause. This is the key to migrate joins during Oracle to PostgreSQL code conversion.

I. When filter conditions have (+) operator

SELECT * 
FROM TBL1, TBL2, TBL3, TBL4 
WHERE TBL3.Col2 = TBL1.Col2 
 AND TBL2.Col1 = TBL1.Col1 
 AND TBL4.Col3(+) = '100' 
 AND TBL4.Col4(+)=TBL1.Col4;

SELECT * 
FROM TBL1
JOIN TBL2 ON TBL2.Col1 = TBL1.Col1
JOIN TBL3 ON TBL3.Col2 = TBL1.Col2
LEFT JOIN TBL4 ON TBL4.Col4=TBL1.Col4 AND TBL4.Col3 = '100';

II. When filter conditions do not have (+) operator

Conditions in WHERE clause specified if do not have a (+) operator then they are kept as Filter conditon during migration.

SELECT * 
FROM TBL1, TBL2, TBL3, TBL4 
WHERE TBL3.Col2 = TBL1.Col2 
 AND TBL2.Col1 = TBL1.Col1 
 AND TBL4.Col3 = '100' 
 AND TBL4.Col4(+)=TBL1.Col4;

SELECT * 
FROM TBL1
JOIN TBL2 ON TBL2.Col1 = TBL1.Col1
JOIN TBL3 ON TBL3.Col2 = TBL1.Col2
LEFT JOIN TBL4 ON TBL4.Col4=TBL1.Col4 
WHERE TBL4.Col3 = '100';

Take Aways – Key to migrate joins from Oracle to PostgreSQL

• When multiple tables are specified in the FROM clause, then they are joined based on the order specified in FROM clause, not on the join conditions mentioned in WHERE clause.
• Except the native outer join syntaxes, other joins specified in Oracle works same and no change required while migrating to PostgreSQL.
• All join/filter conditions with a (+) operator has to be part of the ON clause.