<

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

Published on
7,158 Points
1,458 Views
2 Endorsements
Last Modified:
PortletPaul
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT
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) 

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 

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
  

Open in new window


Which one is the relevant table? above it has to be the alias pedp because the alias busr is the "from table" and that does not have any " ON " conditions. Making the choice of relevant table is vitally important.

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) 

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) 

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)

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)

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.


INNER JOIN
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

Open in new window



CROSS JOIN
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'

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.


RIGHT OUTER JOIN
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'

Open in new window



T-SQL *= or =*
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).
2
Comment
Author:PortletPaul
1 Comment
 
LVL 34

Expert Comment

by:Mike Eghtebas
As time goes on, the information discussed here will be used more often because the SQL syntaxes has to be updated. Thank you for this article. I will come back to this article over and over again because this is not for one sitting.

Mike
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Join & Write a Comment

With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month