Feeling ANSI About Oracle Join Syntax?

26 12 2010

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.


Actions

Information

9 responses

26 12 2010
Tim Hall

Hi.

Funny. I would say the opposite in terms of readability. I think the ANSI way is mush more readable than the Oracle way. I switched almost as soon as it became available in 9i. Probably the biggest thing that “turned” me was dealing with developers from a multi-engine background. It just got easier to use ANSI than having to keep reminding people what all the (+) signs meant. :)

I know formatting is a personal thing, but I think the formatting you use makes it very difficult to read. I think of each join as a single line in the from clause If the line gets too wide then I wrap it, but if possible it stays on the same line. So that statement you listed that looked a total nightmare would look like 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%'; 

Pretty simple. Of course, if you don’t like the ANSI syntax you probably still think it looks like a nightmare. :)

I agree the bug thing is a bit of a problem. In one of the 10g versions you had to write all outer joins as left outer joins because right outer joins just didn’t work. :)

Cheers

Tim…

27 12 2010
Charles Hooper

Tim,

Thanks for stopping by and for reformatting my SQL statement. I am still trying to find the best format for ANSI style SQL statements – I like the ability of vertically scanning a SQL statement to be able to pick out the list of tables that are involved in the SQL statement, as I am able to do with the Oracle specific join syntax. That approach seems to make a mess when applied to ANSI syntax (maybe I just need to indent the ON clause another 4 spaces). Your reformatted version of the SQL statement is easier on the eyes than my version – but I still miss the ability to quickly see the list of tables and the restrictions applied to each table. :-)

I am a little surprised how many ANSI related bugs are fixed in 11.2.0.2 or 12.1, but then I suppose that each transformation that a typical SQL statement using Oracle syntax is subjected to must also be considered for those SQL statements using ANSI syntax (if the transformation typically happens before the ANSI to Oracle syntax conversion) – if 11.2.0.1 introduces a new transformation to better optimize a SQL statement, it could potentially break the ANSI equivalent SQL statement.

27 12 2010
Martin Berger

Thank you for the elaborated blog!
Once again you answered more questions than I initially asked ;-)

27 12 2010
Charles Hooper

Thanks for the compliment, Martin. I have plenty of answers for questions that I do not yet know (a solution in search of the perfect problem). :-)

27 12 2010
William Robertson

Following on from Tim’s point, it also doesn’t help that your examples are all in uppercase and you are using the AFAIK redundant OUTER and INNER keywords. (Are they required in SQL Server?) I just write JOIN and LEFT JOIN (while cursing the geniuses who came up with those meaningless keywords). I also add a blank line around each join clause, and indent them all so that the SELECT-FROM-WHERE structure is clearly visible.

27 12 2010
Charles Hooper

Hi William,

Thank you for the comment and the suggested reformatting. I adopted the rather rigid uppercase formatting/indenting roughly 10 or 11 years ago, and that formatting doesn’t seem to flow well for the ANSI style join syntax. With the following as your starting point, how would you adjust the formatting to make the ANSI style join easier to read?

select
  co.id,
  s.shipped_date,
  col.line_no,
  p.id,
  p.description,
  sl.shipped_qty
from
  customer_order co
  join cust_order_line col on co.id=col.cust_order_id
 
  join part p on col.part_id=p.id
 
  left join shipper_line sl on col.cust_order_id=sl.cust_order_id
    and col.line_no=sl.cust_order_line_no
 
  left join shipper s on sl.packlist_id=s.packlist_id
where
  coalesce(sl.shipped_qty,1) > 0
  and p.description like 'PUMP%';

If you would like to include a formatted code section in your reply, please use a code tag in square brackets before the section and a /code tag in square brackets after the section.

27 12 2010
William Robertson

That’s pretty much what I use. I got into the habit of upper-casing keywords twenty or so years ago, but part of me secretly likes all-lowercase (more than all-uppercase, certainly). I would just add one more blank line before ‘join cust_order_line’. Like Tim, I quite like one line per join clause when it’s simple but switch to multi-line at the first sign of complexity. I would tend to do the lot, i.e. a new line before all ON clauses. I appreciate this leaves the table list un-aligned (customer, cust_order, shipper etc start at different places) which I regret as I like a neat list, but I still think the resulting ANSI FROM clause walks you through the joins better than the old way. It’s also much easier to switch between inner and outer joins when tracing a “why am I getting no rows back?” problem.

I would also place each new table’s column first in its ON condition for reasons done to death here: http://jonathanlewis.wordpress.com/2006/11/02/clarity-clarity-clarity, but basically to be consistent with ‘where empno = 7839′ and ‘where deptno is not null’ type of constructions (i.e. rather than ‘where 7839 = empno’ and, umm, ‘where null is not deptno’). Apparently some people’s brains are wired the other way around to mine, but I always have to edit the code to read that way before I can make any sense of it. That gives me a FROM clause along the lines of:

FROM   customer_order co

       JOIN cust_order_line col
       ON   col.cust_order_id = co.id

       JOIN part p
       ON   p.id = col.part_id

       LEFT JOIN shipper_line sl
       ON   sl.cust_order_id = col.cust_order_id
       AND  sl.cust_order_line_no = col.line_no

       LEFT JOIN shipper s
       ON   s.packlist_id = sl.packlist_id

WHERE  COALESCE(sl.shipped_qty,1) > 0
AND    p.description LIKE 'PUMP%';
28 12 2010
Charles Hooper

William (and Tim),

Thank you for taking the time to reformat the SQL statement, demonstrating your formatting approach for ANSI style SQL statements. When there are three or more tables accessed, your formatting approach helps considerably to improve readability.

Also, thank you for helping to add value to this blog article.

27 12 2010
William Robertson

…of course, that’s just the readability aspect. It is rather worrying to read about ANSI join bugs in 11g – I had rather hoped they mostly went away around 9.2. I haven’t come across one myself. And of course as Jonathan Lewis mentioned recently, the implementation adds an internal transformation that can lead to odd ‘why is my hint being ignored?’ issues (though there are enough of those already for me to be pretty much expecting them these days).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 147 other followers

%d bloggers like this: