Oracle – for when it was like that when you got there

Main menu

Post navigation

ANSI SQL Huh, What is is good for ? Er, Outer Joins Actually

When Oracle first introduced ANSI syntax to become ANSI SQL 92 compliant, the general idea was that the traditional Oracle syntax would do exactly the same as this new fangled ANSI stuff.
You wouldn’t have to start coding LEFT INNER JOINs everywhere and you could pretty much go on your way unmolested by so much syntactic furniture.

This was the approach I’d followed quite happily for many years. Sure, I had moved away from DECODE toward CASE as I think the code tends to be more easy to follow, But all that extra typing to code an ANSI Join – a waste of valuable typing molecules….or so I thought.

Recently, I came across a situation where I needed to outer join a table to two other tables.
Now, in honour of the man who brought this to my attention – a Business Analyst…and an Irishman…on the day that Ireland beat England, yes, at cricket…(thanks Gavan)…

Hmmm – only 10 rows returned. Still, never mind, that’s what outer joins are for …

SELECT cg.country_name, test.current_ranking, odi.current_ranking
FROM cricket_giants cg, cg_tests test, cg_odis odi
WHERE cg.country_code(+) = test.country_code
AND cg.country_code(+) = odi.country_code
/
WHERE cg.country_code(+) = test.country_code
*
ERROR at line 3:
ORA-01417: a table may be outer joined to at most one other table

Hmmm, bit of a rethink required. After all, it’s not like it’s going to work any differently with ANSI syntax is it ?

OK, so I’m still not going to abandon my long-held preference for the humble “=” sign over all that LEFT INNER JOIN malarky. Outer joins however, especially in this particular instance, are probably worth a rethink.
In the meantime, I’ve had to dig out my emergency backup nationality…I just hope New Zealand don’t come up against Ireland in the Quarter-Finals.