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)…

CREATE TABLE cricket_giants ( 
    country_code VARCHAR2(3) PRIMARY KEY, 
    country_name VARCHAR2(30) NOT NULL)    
/ 

CREATE TABLE cg_tests ( 
    country_code VARCHAR2(3) REFERENCES cricket_giants(country_code), 
    first_test_yr NUMBER(4), 
    current_ranking NUMBER(2)) 
/ 

CREATE TABLE cg_odis( 
    country_code VARCHAR2(3) REFERENCES cricket_giants( country_code), 
    first_odi_yr NUMBER(4), 
    current_ranking NUMBER(2)) 
/ 

And here’s the data ( note for any cricket stattos, I’m not sure that these dates are accurate. Look I’m just trying to demo some SQL here !)

DECLARE 
    PROCEDURE ins( p_code VARCHAR2, p_name VARCHAR2) IS 
        BEGIN 
            INSERT INTO cricket_giants( country_code, country_name) 
            VALUES( p_code, p_name); 
        END ins; 
BEGIN 
    ins('AUS', 'AUSTRALIA'); 
    ins('BAN', 'BANGLADESH'); 
    ins('CAN', 'CANADA'); 
    ins('ENG', 'ENGLAND'); 
    ins('IND', 'INDIA'); 
    ins('IRE', 'IRELAND'); 
    ins('KEN', 'KENYA'); 
    ins('NED', 'NETHERLANDS'); 
    ins('NZ', 'NEW ZEALAND'); 
    ins('PAK', 'PAKISTAN'); 
    ins('RSA', 'SOUTH AFRICA'); 
    ins('SL', 'SRI LANKA'); 
    ins('WI', 'WEST INDIES'); 
    ins('ZIM', 'ZIMBABWE'); 
    ins('ROW', 'REST OF THE WORLD'); 
END; 
/

DECLARE 
    PROCEDURE ins( p_code VARCHAR2, p_year NUMBER, p_ranking NUMBER) IS 
        BEGIN 
            INSERT INTO cg_tests( country_code, first_test_yr, current_ranking) 
            VALUES( p_code, p_year, p_ranking); 
        END ins; 
BEGIN 
    ins('AUS', 1877, 5); 
    ins('BAN', 2000, 9); 
    ins('ENG', 1877, 4); 
    ins('IND', 1936, 1); 
    ins('NZ', 1933, 7); 
    ins('PAK', 1948, 6); 
    ins('RSA', 1892, 2); 
    ins('SL', 1982, 3); 
    ins('WI', 1928, 8); 
    ins('ZIM', 1996, 10); 
    ins('ROW', 1970, NULL); 
END; 
/

DECLARE 
    PROCEDURE ins( p_code VARCHAR2, p_year NUMBER, p_ranking NUMBER) IS 
        BEGIN 
            INSERT INTO cg_odis( country_code, first_odi_yr, current_ranking) 
            VALUES( p_code, p_year, p_ranking); 
        END ins; 
BEGIN 
    ins('AUS', 1972, 1); 
    ins('BAN', 2000, 8); 
    ins('CAN', 1975, 14); 
    ins('ENG', 1972, 5); 
    ins('IND',1974,2); 
    ins('IRE',1996,10); 
    ins('KEN',1999,13); 
    ins('NED',1996,12); 
    ins('NZ',1973,7); 
    ins('PAK',1974,6); 
    ins('RSA',1990,4); 
    ins('SL',1975,3); 
    ins('WI',1973,9); 
    ins('ZIM',1975,11); 
END; 
/

Now, if I want to get each country’s current ranking in both Tests and ODIs …

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 
/ 

COUNTRY_NAME	     CURRENT_RANKING CURRENT_RANKING 
-------------------- --------------- --------------- 
AUSTRALIA			   5		   1 
BANGLADESH			   9		   8 
ENGLAND 			   4		   5 
INDIA				   1		   2 
NEW ZEALAND			   7		   7 
PAKISTAN			   6		   6 
SOUTH AFRICA		   2		   4 
SRI LANKA			   3		   3 
WEST INDIES			   8		   9 
ZIMBABWE			  10		  11 

10 rows selected. 

SQL> 

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 ?

SELECT cg.country_name, test.current_ranking, odi.current_ranking 
FROM cricket_giants cg 
LEFT OUTER JOIN cg_tests test 
ON cg.country_code = test.country_code 
LEFT OUTER JOIN cg_odis odi 
ON cg.country_code = odi.country_code 
/ 

COUNTRY_NAME	     CURRENT_RANKING CURRENT_RANKING 
-------------------- --------------- --------------- 
INDIA				   1		   2 
SOUTH AFRICA		   2		   4 
SRI LANKA			   3		   3 
ENGLAND 			   4		   5 
AUSTRALIA			   5		   1 
PAKISTAN			   6		   6 
NEW ZEALAND			   7		   7 
WEST INDIES			   8		   9 
BANGLADESH			   9		   8 
ZIMBABWE			  10		  11 
IRELAND 			        	  10 
NETHERLANDS					      12 
KENYA						      13 
CANADA						      14 
REST OF THE WORLD 

15 rows selected. 

SQL> 

Oh.

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.