*= (+) =* Ye Olde Joins (how to replace them)

Confronted with some SQL you don't know can be a daunting task.
It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as:
(+)     as used in Oracle;     *=     =*    as used in Sybase & SQL Server.

This bright new age uses ANSI standard syntax for joins, such as INNER JOIN or LEFT OUTER JOIN.
So how does one get rid of those awful old secret codes and use the far better, more standard, ANSI join syntax?

The following is my attempt to explain step by step how I do it. Please note that while this article uses an Oracle example, the process applies to Ye Olde SQL regardless of vendor.

We will start with the following SQL. This will be new and unfamiliar to you; Well it was new and unfamiliar to me also (as I didn't invent it) but for this article that is perfect. You do not need to understand the tables/fields for this exercise, Also, we care only about the FROM and WHERE clauses here.

select *
                      from proj.busrepts busr, proj.pbase pb, proj.WQuota wq, finance.pedproj pedp, finance.qiiview qii 
                      where busr.projectid = pedp.projectid(+) and 
                      busr.program = pedp.pedprogram  and 
                      busr.patnum   =  pb.fileno(+) and 
                      busr.curtype like '%Self%' and 
                      busr.wq_statusid = wq.statusid(+) and 
                      busr.DOS >= TO_DATE('01-JUL-2010','DD-MON-YYYY') and 
                      busr.balance >5  and 
                      (qii.address not in ('1 Pudding Lane', 'Diagon Alley') and qii.address not like '%Home%' 
                      and qii.address not like '%HOME%' and qii.address not like 'Unk%' 
                      and qii.address not like 'UNK%') 
                      and busr.projectid = qii.projectid(+) and 
                      busr.program = qii.program 
                      and wq.name in ('Project Invoice Cycle Alpha') 
                      and busr.program in (51,91,161,211,231,271,351) 
                      and busr.wq_followup = TRUNC(sysdate) 

Select all Open in new window

1.

Divide the existing from clause into one line per table by entering a new line at each comma.
Tip poorsql.com can do this step for you (and more formatting etc.)

2.

Add " ON " after each table except the first one

from proj.busrepts busr
                      , proj.pbase pb ON 
                      , proj.WQuota wq ON 
                      , finance.pedproj pedp ON 
                      , finance.qiiview qii ON 

Select all Open in new window

3.

CUT elements of the where clause that reference tables on both sides,
e.g. table1.field = table2.field or table1.field = table2.field(+)
(include any (+) or *= or =* in the CUT text) and then PASTE it against the relevant table in the from clause.

from proj.busrepts busr
                      , proj.pbase pb ON 
                      , proj.WQuota wq ON 
                      , finance.pedproj pedp ON busr.projectid = pedp.projectid(+) --<< PASTE here
                      , finance.qiiview qii ON  
                      where  and --CUT was here
                        

Select all Open in new window

4. Repeat step 3

Repeat until all table.field = table.field conditions have been moved to the FROM clause. It may be necessary to include further conjunctions of AND / OR as you move these conditions. At this point the SQL will look like mice have started to eat it.

from proj.busrepts busr
                      , proj.pbase pb ON busr.patnum   =  pb.fileno(+)
                      , proj.WQuota wq ON busr.wq_statusid = wq.statusid(+) 
                      , finance.pedproj pedp ON busr.projectid = pedp.projectid(+) AND busr.program = pedp.pedprogram 
                      , finance.qiiview qii ON busr.projectid = qii.projectid(+) AND busr.program = qii.program 
                      where and 
                       and 
                       and 
                      busr.curtype like '%Self%' and 
                      and 
                      busr.DOS >= TO_DATE('01-JUL-2010','DD-MON-YYYY') and 
                      busr.balance >5  and 
                      (qii.address not in ('1 Pudding Lane', 'Diagon Alley') and qii.address not like '%Home%' 
                      and qii.address not like '%HOME%' and qii.address not like 'Unk%' 
                      and qii.address not like 'UNK%') 
                      and and 
                      
                      and wq.name in ('Project Invoice Cycle Alpha') 
                      and busr.program in (51,91,161,211,231,271,351) 
                      and busr.wq_followup = TRUNC(sysdate) 

Select all Open in new window

5.

If from clause lines contains (+) change the comma of that line to LEFT JOIN, else, change the comma to INNER JOIN.

6.

If a LEFT JOIN exists, and that table is still referenced in the where clause, also move those conditions to the join of that table. At this stage the query will definitely look strange

from proj.busrepts busr
                      LEFT JOIN proj.pbase pb ON busr.patnum   =  pb.fileno(+)
                      LEFT JOIN proj.WQuota wq ON busr.wq_statusid = wq.statusid(+) and wq.name in ('Project Invoice Cycle Alpha') 
                      LEFT JOIN finance.pedproj pedp ON busr.projectid = pedp.projectid(+) AND busr.program = pedp.pedprogram 
                      LEFT JOIN finance.qiiview qii ON busr.projectid = qii.projectid(+) AND busr.program = qii.program and 
                      (qii.address not in ('1 Pudding Lane', 'Diagon Alley') and qii.address not like '%Home%' 
                      and qii.address not like '%HOME%' and qii.address not like 'Unk%' 
                      and qii.address not like 'UNK%') 
                      where and 
                       and 
                       and 
                      busr.curtype like '%Self%' and 
                      and 
                      busr.DOS >= TO_DATE('01-JUL-2010','DD-MON-YYYY') and 
                      busr.balance >5  
                      and and 
                      
                      
                      and busr.program in (51,91,161,211,231,271,351) 
                      and busr.wq_followup = TRUNC(sysdate) 

Select all Open in new window

7. Tidy up

Now remove all secret codes in the FROM clause, and remove all orphaned conjunctions and any unwanted white space in the WHERE clause created by moving conditions.

from proj.busrepts busr
                      LEFT JOIN proj.pbase pb ON busr.patnum   =  pb.fileno
                      LEFT JOIN proj.WQuota wq ON busr.wq_statusid = wq.statusid and wq.name in ('Project Invoice Cycle Alpha') 
                      LEFT JOIN finance.pedproj pedp ON busr.projectid = pedp.projectid AND busr.program = pedp.pedprogram 
                      LEFT JOIN finance.qiiview qii ON busr.projectid = qii.projectid AND busr.program = qii.program and 
                      (qii.address not in ('1 Pudding Lane', 'Diagon Alley') and qii.address not like '%Home%' 
                      and qii.address not like '%HOME%' and qii.address not like 'Unk%' 
                      and qii.address not like 'UNK%') 
                      where busr.curtype like '%Self%' and 
                      and busr.DOS >= TO_DATE('01-JUL-2010','DD-MON-YYYY')
                       and busr.balance >5  
                      and busr.program in (51,91,161,211,231,271,351) 
                      and busr.wq_followup = TRUNC(sysdate)

Select all Open in new window

8.

Check the sequence of table references in the join conditions. An ANSI join cannot refer to a table if it hasn't already been joined. In our example we don't have that problem - but be on the lookout for it.

0. Optional (Start or End)

Use a formatting assistant. Many tools have these built in but http://poorsql.com/ is very helpful if you don't have one handy. In fact I suggest taking the code block above and pasting into poorsql.com, which produces the following depending on you use of that site's preferences:

FROM proj.busrepts busr
                      LEFT JOIN proj.pbase pb ON busr.patnum = pb.fileno
                      LEFT JOIN proj.WQuota wq ON busr.wq_statusid = wq.statusid
                      	AND wq.NAME IN ('Project Invoice Cycle Alpha')
                      LEFT JOIN finance.pedproj pedp ON busr.projectid = pedp.projectid
                      	AND busr.program = pedp.pedprogram
                      LEFT JOIN finance.qiiview qii ON busr.projectid = qii.projectid
                      	AND busr.program = qii.program
                      	AND (
                      		qii.address NOT IN ('1 Pudding Lane', 'Diagon Alley')
                      		AND qii.address NOT LIKE '%Home%'
                      		AND qii.address NOT LIKE '%HOME%'
                      		AND qii.address NOT LIKE 'Unk%'
                      		AND qii.address NOT LIKE 'UNK%'
                      		)
                      WHERE busr.curtype LIKE '%Self%'
                      	AND busr.DOS >= TO_DATE('01-JUL-2010', 'DD-MON-YYYY')
                      	AND busr.balance > 5
                      	AND busr.program IN (51, 91, 161, 211, 231, 271, 351)
                      	AND busr.wq_followup = TRUNC(sysdate)

Select all Open in new window

The above example was derived from a question here at EE and it happens that all the joins in that example are LEFT OUTER JOINS. Let's now look at a series of examples for other join types.


These are usually quite simple to identify once you go through the steps above. INNER JOINS do not have "secret codes" in Ye Olde SQL syntax. Your input sql might look like line 1 below:

from table1, table2 where table1.id = table2.fk_id;
                      
                      -- step 1
                      from table1
                      , table2 ON
                      where table1.id = table2.fk_id
                      
                      -- step 3
                      from table1
                      , table2 ON table1.id = table2.fk_id
                      where 
                      
                      -- step 7
                      from table1
                      INNER JOIN table2 ON table1.id = table2.fk_id

Select all Open in new window

This can be the hardest of all to identify in Ye Olde SQL syntax, simply because the absence of any reference between 2 tables produces a "Cartesian product" (or CROSS JOIN). So here we are on guard to look out for something that is absent

from table1, table2 where table1.nm = 'butterfly'
                      
                      -- step 1
                      from table1
                      , table2 ON
                      where table1.nm = 'butterfly'
                      
                      -- step 3
                      from table1
                      , table2 ON 
                      where table1.nm = 'butterfly'
                      
                      -- HOLD ON, there is nothing like: table.field = table.field
                      -- nothing that refers to table2 at all in the where clause, could it just be a mistake? (needs testing!)
                      
                      -- step 7
                      from table1
                      CROSS JOIN table2
                      where table1.nm = 'butterfly'

Select all Open in new window

So, if there is nothing in the where clause that references a table and consequently there is nothing after ON, this is either a deliberate CROSS JOIN - or - it is a mistake. Believe me when I say this mistake is more common than you may think, so be on guard for tables left with "nothing" to join by.


In Ye Olde syntax sequence wasn't too much of a concern and because you can always flip sides of an equals operator table1.fk_id(+) = table2.id  or table2.id = table1.fk_id(+)  are equivalents. However there is a tendency for folks to reference the tables in the same left to right sequence you find in Ye Olde syntax. It can get confusing but going through the steps we start to see clues and how to deal with those:

from table1, table2 where table1.fk_id(+) = table2.id and table2.code = 'x'
                      
                      -- step 1
                      from table1
                      , table2 ON
                      where 
                      and table2.code = 'x'
                      
                      -- step 3
                      from table1
                      , table2 ON table1.fk_id(+) = table2.id 
                      where 
                      and table2.code = 'x'
                      
                      -- HOLD ON the secret code applies to the "FROM table"
                      -- so, NEED something different, could be
                      -- A: a right outer join
                      
                      -- step 7A
                      from table1
                      RIGHT JOIN table2 ON table1.fk_id = table2.id 
                      where table2.code = 'x'
                      
                      -- B: flip the order of tables
                      
                      -- step 7B
                      from table2
                      LEFT JOIN table1 ON table2.id = table1.fk_id
                      where table2.code = 'x'

Select all Open in new window

Although not nice to work with, and certainly not as good as the ANSI syntax, these operators actually tell us if they are LEFT JOIN (*=) or RIGHT JOIN (=*) by figuring out which side of the equal symbol you see the asterisk. Suggested reference (Sybase).