Feeling ANSI About Oracle Join Syntax?

December 26, 2010

(Forward to the Next Post in the Series)

Yesterday I started reading another book on the topic of Oracle SQL (for the moment I will keep the title of the book a mystery).  I am not much of a fan of ANSI style syntax – that syntax style is easy to read when there are just two tables involved, but I tend to become lost when several tables are listed with left outer joins to some tables and equi-joins to other tables.  Take for instance the following example which I encountered in an ERP mailing list:

SELECT CUSTOMER_ORDER.ID, SHIPPER.SHIPPED_DATE, CUST_ORDER_LINE.LINE_NO,
PART.ID, PART.DESCRIPTION, SHIPPER_LINE.SHIPPED_QTY
FROM CUSTOMER_ORDER
LEFT JOIN SHIPPER ON CUSTOMER_ORDER.ID = SHIPPER.CUST_ORDER_ID
INNER JOIN SHIPPER_LINE ON SHIPPER.PACKLIST_ID =
SHIPPER_LINE.PACKLIST_ID AND SHIPPER_LINE.SHIPPED_QTY > 0
LEFT JOIN CUST_ORDER_LINE ON CUSTOMER_ORDER.ID =
CUST_ORDER_LINE.CUST_ORDER_ID INNER JOIN PART ON PART.ID =
CUST_ORDER_LINE.PART_ID AND PART.DESCRIPTION LIKE 'PUMP%'

The above query did not work quite as the person wanted, so he asked for some assistance:

“The only thing with this is that it doesn’t seem to show me Customer
order lines where no shipments have occurred. I.E all returned records
have a Shipped_date and there should be some that don’t have
Shipped_date.”

After looking at the problem for a couple of minutes, I proposed the following SQL statement:

SELECT
  CO.ID,
  S.SHIPPED_DATE,
  COL.LINE_NO,
  P.ID,
  P.DESCRIPTION,
  SL.SHIPPED_QTY
FROM
  CUSTOMER_ORDER CO,
  CUST_ORDER_LINE COL,
  PART P,
  SHIPPER S,
  SHIPPER_LINE SL
WHERE
  CO.ID=COL.CUST_ORDER_ID
  AND COL.PART_ID=P.ID
  AND COL.CUST_ORDER_ID=SL.CUST_ORDER_ID(+)
  AND COL.LINE_NO=SL.CUST_ORDER_LINE_NO(+)
  AND SL.PACKLIST_ID=S.PACKLIST_ID(+)
  AND NVL(SL.SHIPPED_QTY,1) > 0
  AND P.DESCRIPTION LIKE 'PUMP%';

The above is nice and simple Oracle specific join syntax that seemed to meet the OP’s requirements.  But wait, the OP’s company is considering a switch from Oracle Database to SQL Server!  Bang head on the table until this makes sense (little floating dots, kind of looks like ants see).  Here, try this:

SELECT
  CO.ID,
  S.SHIPPED_DATE,
  COL.LINE_NO,
  P.ID,
  P.DESCRIPTION,
  SL.SHIPPED_QTY
FROM
  CUSTOMER_ORDER CO
INNER JOIN
  CUST_ORDER_LINE COL
ON
  CO.ID=COL.CUST_ORDER_ID
INNER JOIN
  PART P
ON
  COL.PART_ID=P.ID
LEFT OUTER JOIN
  SHIPPER_LINE SL
ON
  COL.CUST_ORDER_ID=SL.CUST_ORDER_ID
  AND COL.LINE_NO=SL.CUST_ORDER_LINE_NO
LEFT OUTER JOIN
  SHIPPER S
ON
  SL.PACKLIST_ID=S.PACKLIST_ID
WHERE
  COALESCE(SL.SHIPPED_QTY,1) > 0
  AND P.DESCRIPTION LIKE 'PUMP%';

So, which of the above two SQL statements is easier to read?

Still not convinced?  Let’s look at the FROM clause of another simple SQL statement.

Oracle specific join syntax (edit: Dec 26, 2010 2.5 hours after the initial post: the fifth line in the WHERE clause should show AND T3.VENDOR_ID(+) = ‘SAM’ due to the outer join between tables T1 and T3 – with that fix is the query section still equivalent to the ANSI version?  Something also needs to be done with the last line in the WHERE clause due to the outer join between tables T3 and T4):

FROM
  T1,
  T2,
  T3,
  T4
WHERE
  T1.PRODUCT_CODE='10'
  AND T1.PART_ID=T2.PART_ID(+)
  AND T2.DRAWING_REV_NO='20'
  AND T1.PART_ID=T3.PART_ID(+)
  AND T3.VENDOR_ID='SAM'
  AND T3.PART_ID=T4.PART_ID(+)
  AND T4.DESCRIPTION LIKE 'PAINT%';

ANSI join syntax:

FROM
  T1
LEFT OUTER JOIN
  T2
ON
  T1.PART_ID=T2.PART_ID
LEFT OUTER JOIN
  T3
ON
  T1.PART_ID=T3.PART_ID
LEFT OUTER JOIN
  T4
ON
  T3.PART_ID=T4.PART_ID
WHERE
  T1.PRODUCT_CODE='10'
  AND T2.DRAWING_REV_NO='20'
  AND T2.LOT_ID='0'
  AND T2.SPLIT_ID='0'
  AND T3.VENDOR_ID='SAM'
  AND T4.DESCRIPTION LIKE 'PAINT%';

When written with the Oracle specific join syntax, I can quickly see the list of tables that are accessed, and I can see how the results from each table will be restricted as the rows are retrieved.  Oracle Database will actually try to convert the ANSI style join syntax back into Oracle join syntax (this can be seen by enabling a 10053 trace), and there are several cases where bugs will cause the wrong results to be returned to the client.  In the above portion of a SQL statement, Oracle’s optimizer will not necessarily join table T1 to table T2 and then join the result to T3 and then join that result to T4.  The optimizer could join the tables in just about any order, possibly even joining two tables that have no join condition between them (in the process creating a Cartesian product).

The OP mentioned reading a book titled “SQL in 10 Minutes”, or something like that, which is one that I read back in 2000 when I was working with Oracle Database 8.0.5 – that book only describes the ANSI style join syntax.  The 8.0.5 version did not support the ANSI join syntax, and neither did 8.1.7 (8i), so I used the Oracle specific syntax until jumping to Oracle Database 10.2.0.2 in 2006.  There are some cases where the Oracle specific syntax does not translate directly into the ANSI style syntax, where the two syntaxes appear to be requesting the same information, but actually return different information (and that result is not considered a bug).  A couple of years ago I participated in a Usenet thread that included one such query.  This blog article by a member of the Oracle Optimizer group shows an example of that type of query – I believe it is examples C. and D.).

Considering that the Oracle query optimizer transforms the ANSI join syntax into Oracle specific syntax, it seems that there is always a risk of triggering an Oracle bug during the transformation (these bugs are less common in recent Oracle releases).  Bugs?  Just a very small sampling from Metalink (MOS) followed by a couple of mentions from other blogs:

• Doc ID 9050716.8, Bug 9050716 “Dumps on kkqstcrf with ANSI joins and Join Elimination”, affects Oracle Database release versions below 11.2.0.2
• Doc ID 5188321.8, Bug 5188321 “Wrong results (no rows) OR ORA-1445 from ANSI outer join”, affects Oracle Database release versions below 11.1.0.6
• Doc ID 5368296.8, Bug 5368296 “ANSI join SQL may not report ORA-918 for ambiguous column”, affects Oracle Database release versions below 11.1.0.6
• Doc ID 7420504.8, Bug 7420504 “Wrong Results with ANSI queries and fix for bug 7345484”, affects versions 10.2.0.3 through 11.1.0.7
• Doc ID 9189070.8, Bug 9189070 “Dump (kkoiqb) parsing ANSI SQL with disjunctive predicate in ‘ON’ clause”, affects versions below 11.2.0.2
• Doc ID 5864217.8, Bug 5864217 “OERI [kkoljt1] from ANSI query with join elimination”, affects Oracle Database release versions below 11.1.0.6
• Doc ID 6050882.8, Bug 6050882 “Wrong results from ANSI outer join with fix for bug 4967068 or 5864217”, affects Oracle Database release versions below 11.1.0.6
• Doc ID 7001990.8, Bug 7001990 “Wrong result (null column) on ansi outer join query and index access”, affects Oracle Database release versions below 11.1.0.7
• Doc ID 9371323.8, Bug 9371323 “OERI [rwoirw: check ret val] on INSERT ALL with ANSI join”, affects Oracle Database release versions below 11.2.0.2
• Doc ID 5702433.8, Bug 5702433 “ORA-918 from ANSI joine with a CONTAINS clause”, affects Oracle Database release versions below 11.2.0.1
• Doc ID 6033480.8, Bug 6033480 “High parse time of query when using ANSI style joins”, affects Oracle Database release versions below 11.1.0.6
• Doc ID 7710538.8, Bug 7710538 “ORA-904 from UNION ALL with ANSI join”, affects Oracle Database release versions below 11.2.0.1
• Doc ID 8547175.8, Bug 8547175 “FGA does not work correctly with ANSI joins”, affects Oracle Database release versions below 11.2.0.1
• Doc ID 8782089.8, Bug 8782089 “OERI [15160] on ANSI join”, affects Oracle Database release versions below 11.2.0.2
• Doc ID 9395765.8, Bug 9395765 “ORA-907 / wrong plan from query with ANSI FULL OUTER join”, affects Oracle Database release versions below 11.2.0.2
• Doc ID 9713012.8, Bug 9713012 “Wrong results from push predicate on ANSI SQL”, affects Oracle Database release versions below 11.2.0.2
• ANSI – argh
• ANSI SQL
• When ANSI SQL Join Syntax Does Not Work in Oracle
• ANSI Outer Joins And Lateral Views (relates to the above referenced article by the Oracle Optimizer group)

I recently saw an interesting message thread on the Oracle-L listserve that asked whether or not it was a bad idea to mix ANSI style joins with Oracle specific style joins.  Randolf and I discussed the same topic while working on the “Expert Oracle Practices” book – if I recall correctly, we determined that while it might work, it is a bad idea; the same execution plans were created for both of the simple test queries.  But why were the execution plans the same?  We need a simple test case script for help:

First, let’s create three very simple tables, making certain to declare the first column in each table as NOT NULL (this is important for one section of the test script):

CREATE TABLE T1(
  C1 NUMBER NOT NULL,
  C2 DATE);

CREATE TABLE T2(
  C1 NUMBER NOT NULL,
  C2 DATE);

CREATE TABLE T3(
  C1 NUMBER NOT NULL,
  C2 DATE);

INSERT INTO
  T1
SELECT
  ROWNUM*3 C1,
  TRUNC(SYSDATE)+(ROWNUM*3) C2
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

INSERT INTO
  T2
SELECT
  (ROWNUM-30)*4 C1,
  TRUNC(SYSDATE)+(ROWNUM*4) C2
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

INSERT INTO
  T3
SELECT
  (ROWNUM-60)*4 C1,
  TRUNC(SYSDATE)+(ROWNUM*4) C2
FROM
  DUAL
CONNECT BY
  LEVEL<=200;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3')

For the test script we will enable a 10053 trace, execute a SQL statement using Oracle specific join syntax, and then the equivalent SQL statement using ANSI join syntax:

ALTER SESSION SET TRACEFILE_IDENTIFIER='ANSI_TRANSFORM';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1;

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1
INNER JOIN
  T2
ON
  T1.C1=T2.C1;

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1(+);

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1
LEFT OUTER JOIN
  T2
ON
  T1.C1=T2.C1;

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1(+)=T2.C1;

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1
RIGHT OUTER JOIN
  T2
ON
  T1.C1=T2.C1;

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1(+)
UNION ALL
SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1(+)=T2.C1
  AND T1.C1 IS NULL;

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1
FULL OUTER JOIN
  T2
ON
  T1.C1=T2.C1;

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2,
  T3.C2
FROM
  T1,
  T2,
  T3
WHERE
  T1.C1=T2.C1(+)
  AND T2.C1=T3.C1;

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2,
  T3.C2
FROM
  T1
LEFT OUTER JOIN
  T2
ON
  T1.C1=T2.C1,
  T3
WHERE
  T2.C1=T3.C1;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

The second to the last example (line 72) shows a full outer join using Oracle syntax (note that the column C1 must be declared as NOT NULL) and the ANSI full outer join syntax (line 95).  The last example shows what happens when ANSI join syntax is mixed with Oracle join syntax.  The following shows portions of the contents of the 10053 trace file when the script was executed on Oracle Database 11.2.0.1:

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1;

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2" WHERE "T1"."C1"="T2"."C1" 
SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1
INNER JOIN
  T2
ON
  T1.C1=T2.C1;

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2" WHERE "T1"."C1"="T2"."C1"

The final query after transformation is identical for this set of SQL statements.

—-

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1(+);

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2" WHERE "T1"."C1"="T2"."C1"(+) 
SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1
LEFT OUTER JOIN
  T2
ON
  T1.C1=T2.C1;

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2" WHERE "T1"."C1"="T2"."C1"(+)

The final query after transformation is identical for this set of SQL statements.

—-

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1(+)=T2.C1

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2" WHERE "T1"."C1"(+)="T2"."C1" 
SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1
RIGHT OUTER JOIN
  T2
ON
  T1.C1=T2.C1

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2" FROM "TESTUSER"."T2" "T2","TESTUSER"."T1" "T1" WHERE "T1"."C1"(+)="T2"."C1"

The final query after transformation is identical for this set of SQL statements.

—-

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1(+)
UNION ALL
SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1(+)=T2.C1
  AND T1.C1 IS NULL

Final query after transformations:******* UNPARSED QUERY IS *******
 (SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2"
 WHERE "T1"."C1"="T2"."C1"(+))
UNION ALL  (SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2"
 WHERE "T1"."C1"(+)="T2"."C1" AND "T1"."C1" IS NULL) 
SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2
FROM
  T1
FULL OUTER JOIN
  T2
ON
  T1.C1=T2.C1

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VW_FOJ_0"."QCSJ_C000000000300000_0" "C1","VW_FOJ_0"."QCSJ_C000000000300002_1" "C2","VW_FOJ_0"."QCSJ_C000000000300001_2" "C1",
"VW_FOJ_0"."QCSJ_C000000000300003_3" "C2" FROM
 (SELECT "T1"."C1" "QCSJ_C000000000300000_0","T1"."C2" "QCSJ_C000000000300002_1","T2"."C1" "QCSJ_C000000000300001_2",
 "T2"."C2" "QCSJ_C000000000300003_3" FROM "TESTUSER"."T2" "T2" FULL OUTER JOIN "TESTUSER"."T1" "T1" ON "T1"."C1"="T2"."C1") "VW_FOJ_0"

The final query after transformation is definitely not the same for the Oracle specific syntax and the ANSI syntax, even though the rows retrieved were identical.

—-

SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2,
  T3.C2
FROM
  T1,
  T2,
  T3
WHERE
  T1.C1=T2.C1(+)
  AND T2.C1=T3.C1;

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2","T3"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2","TESTUSER"."T3" "T3"
WHERE "T1"."C1"="T2"."C1" AND "T2"."C1"="T3"."C1" 
SELECT
  T1.C1,
  T1.C2,
  T2.C1,
  T2.C2,
  T3.C2
FROM
  T1
LEFT OUTER JOIN
  T2
ON
  T1.C1=T2.C1,
  T3
WHERE
  T2.C1=T3.C1;

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2","T3"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2","TESTUSER"."T3" "T3"
WHERE "T2"."C1"="T3"."C1" AND "T1"."C1"="T2"."C1"

The final query after transformation is identical for this set of SQL statements, but notice that the optimizer apparently “lost” the “outer” portion of the  join between tables T1 and T2.  I do not think that is supposed to happen (or at least it was not intended – probably should have specified AND T2.C1=T3.C1(+) )!  This is the relevant portion of the 10053 trace file, in case anyone wants to investigate:

Join Elimination (JE)   
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2","T3"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2","TESTUSER"."T3" "T3" WHERE "T1"."C1"="T2"."C1"(+) AND "T2"."C1"="T3"."C1"
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2","T3"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2","TESTUSER"."T3" "T3" WHERE "T1"."C1"="T2"."C1"(+) AND "T2"."C1"="T3"."C1"
Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Considering outer-join elimination on query block SEL$1 (#0)
OJE: considering predicate"T1"."C1"="T2"."C1"(+)

rejected
OJE: considering predicate"T2"."C1"="T3"."C1"

OJE:      Converting outer join of T2 and T1 to inner-join.
considered
Registered qb: SEL$6E71C6F6 0x1fbfcba0 (OUTER-JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$6E71C6F6 nbfros=3 flg=0
    fro(0): flg=0 objn=82957 hint_alias="T1"@"SEL$1"
    fro(1): flg=0 objn=82958 hint_alias="T2"@"SEL$1"
    fro(2): flg=0 objn=83037 hint_alias="T3"@"SEL$1"

OJE:   outer-join eliminated
JE:   Considering Join Elimination on query block SEL$6E71C6F6 (#0)
*************************
Join Elimination (JE)   
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2","T3"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2","TESTUSER"."T3" "T3" WHERE "T1"."C1"="T2"."C1" AND "T2"."C1"="T3"."C1"
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2","T2"."C1" "C1","T2"."C2" "C2","T3"."C2" "C2" FROM "TESTUSER"."T1" "T1","TESTUSER"."T2" "T2","TESTUSER"."T3" "T3" WHERE "T1"."C1"="T2"."C1" AND "T2"."C1"="T3"."C1"

Considering the above transformation, I now feel a little ansy about Oracle join syntax.