ANSI Joins

fsher · #1 (**permalink**) 01-23-12, 11:33

For those of you who believe ansi joins are easier to understand, how would you format a nightmare like the below(produced by access) in some way that is understandable. This is not a facetious question, I would like to know because have been trying on and off over a couple of days and putting the joins in the where clause seems so much more straight forward and clearer (not that either way is wonderful for a query like this, but you have to do something).

-- ANSI Joins
SELECT MAP_REGION_OFFICE.REGION_NAME
,MAP_REGION_OFFICE.UND_OFFICE_NAME AS OFFICE
,EL.DESCRIPTION AS UW
,PREMIUM_LOB_MAP.PRODUCT
,P.POL_ALT_ID
,P.POLICY_NUMBER
,P.POL_EFF_DATE
,P.POL_EXP_DATE
,P.CHANGE_EFF_DATE
,P.POLICY_DATE_TIME
,EDIT_SHORT_CODE.DESCRIPTION AS PREM_TRAN_TYPE
,EDIT_SHORT_CODE_1.DESCRIPTION AS NEW_OR_RENEW
,EDIT_LONG_CODE.DESCRIPTION AS RATING_STATE
,CN.CLIENT_NAME
,ADDRESS.STATE AS CLIENT_STATE
,A.AGENCY_NAME
,A.AGENCY_STATE
,F.BROKER_CONT AS BROKER_CONTACT
,Sum(PD.BILLED_PREMIUM) AS PREMIUM
,PREMIUM_LOB_MAP.DESCRIPTION AS LINE_OF_BUS
,MAP_REGION_OFFICE.DATABASE_NAME
FROM ((OFFICE_FILTER INNER
JOIN((((((((((((POLICY AS P INNER JOIN PREMIUM_DETAIL AS PD
ON(P.POLICY_NUMBER = PD.POLICY_NUMBER)
AND (P.POLICY_DATE_TIME = PD.POLICY_DATE_TIME))
INNER JOIN CLIENT_NAMES AS CN
ON P.CLIENT_NUMBER = CN.CLIENT_NUMBER)
INNER JOIN AGENCY AS A
ON P.AGENCY_ID = A.AGENCY_ID)
INNER JOIN FMFC_DETAIL AS F
ON (P.POLICY_NUMBER = F.POLICY_NUMBER)
AND (P.POLICY_DATE_TIME = F.POLICY_DATE_TIME))
INNER JOIN EDIT_LONG_CODE AS EL
ON F.UNDERWRITER = EL.CODE)
INNER JOIN MAP_REGION_OFFICE
ON F.UND_OFFICE = MAP_REGION_OFFICE.OLD_UND_OFFICE)
INNER JOIN PREMIUM_LOB_MAP
ON PD.PREMIUM_LOB = PREMIUM_LOB_MAP.PREMIUM_LOB)
INNER JOIN EDIT_SHORT_CODE
ON P.BILLED_PREMIUM_ID = EDIT_SHORT_CODE.CODE)
INNER JOIN EDIT_SHORT_CODE AS EDIT_SHORT_CODE_1
ON P.NEW_OR_RENEWAL = EDIT_SHORT_CODE_1.CODE)
INNER JOIN BROKER_FILTER
ON A.AGENCY_NUMBER = BROKER_FILTER.AGENCY_NUMBER)
INNER JOIN LOB_FILTER
ON PREMIUM_LOB_MAP.PREMIUM_LOB = LOB_FILTER.PREMIUM_LOB)
INNER JOIN UNDERWRITER_FILTER
ON EL.CODE = UNDERWRITER_FILTER.UW_CODE)
ON OFFICE_FILTER.OFFICE_CODE = MAP_REGION_OFFICE.UND_OFFICE)
INNER JOIN ADDRESS ON CN.CLIENT_NUMBER = ADDRESS.CLIENT_NUMBER)
INNER JOIN EDIT_LONG_CODE
ON F.RTNG_STATE = EDIT_LONG_CODE.CODE
WHERE (((LOB_FILTER****N_CODE) = [ Forms ] ! [ frmMain ] ! [txtSession ])
AND ((UNDERWRITER_FILTER****N_CODE) = [ Forms ] ! [ frmMain ] ! [ txtSession ])
AND ((OFFICE_FILTER****N_CODE) = [ Forms ] ! [ frmMain ] ! [ txtSession ])
AND ((BROKER_FILTER****N_CODE) = [ Forms ] ! [ frmMain ] ! [ txtSession ])
AND ((EL.NAME) = 'UNDERWRITER')
AND ((EL.TBNAME) = 'FMFC_DETAIL')
AND ((P.BILLED_PREMIUM_ID) In ('5', '3', '2', '1'))
AND ((F.UND_OFFICE) <> 'V02')
AND ((EDIT_SHORT_CODE.NAME) = "BILLED_PREMIUM_ID")
AND ((EDIT_SHORT_CODE.TBNAME) = "POLICY")
AND ((EDIT_SHORT_CODE_1.NAME) = "NEW_OR_RENEWAL")
AND ((EDIT_SHORT_CODE_1.TBNAME) = "POLICY")
AND ((MAP_REGION_OFFICE.DATABASE_NAME) = "FMFCRPT")
AND ((ADDRESS.SEQUENCE_NUMBER) = 1)
AND ((EDIT_LONG_CODE.NAME) = "RTNG_STATE")
AND ((EDIT_LONG_CODE.TBNAME) = "FMFC_DETAIL")
AND ((PREMIUM_LOB_MAP.BUS_UNIT) Not In ('PL', 'PROF', 'MGMT')))
GROUP BY MAP_REGION_OFFICE.REGION_NAME
,MAP_REGION_OFFICE.UND_OFFICE_NAME
,EL.DESCRIPTION
,PREMIUM_LOB_MAP.PRODUCT
,P.POL_ALT_ID
,P.POLICY_NUMBER
,P.POL_EFF_DATE
,P.POL_EXP_DATE
,P.CHANGE_EFF_DATE
,P.POLICY_DATE_TIME
,EDIT_SHORT_CODE.DESCRIPTION
,EDIT_SHORT_CODE_1.DESCRIPTION
,EDIT_LONG_CODE.DESCRIPTION
,CN.CLIENT_NAME
,ADDRESS.STATE
,A.AGENCY_NAME
,A.AGENCY_STATE
,F.BROKER_CONT
,PREMIUM_LOB_MAP.DESCRIPTION
,MAP_REGION_OFFICE.DATABASE_NAME
ORDER BY MAP_REGION_OFFICE.UND_OFFICE_NAME
,EL.DESCRIPTION
,PREMIUM_LOB_MAP.PRODUCT
,P.POL_ALT_ID
,P.POLICY_DATE_TIME;

-- EQUI joins
SELECT MAP_REGION_OFFICE.REGION_NAME
,MAP_REGION_OFFICE.UND_OFFICE_NAME AS OFFICE
,EL.DESCRIPTION AS UW
,PREMIUM_LOB_MAP.PRODUCT
,P.POL_ALT_ID
,P.POLICY_NUMBER
,P.POL_EFF_DATE
,P.POL_EXP_DATE
,P.CHANGE_EFF_DATE
,P.POLICY_DATE_TIME
,EDIT_SHORT_CODE.DESCRIPTION AS PREM_TRAN_TYPE
,EDIT_SHORT_CODE_1.DESCRIPTION AS NEW_OR_RENEW
,EDIT_LONG_CODE.DESCRIPTION AS RATING_STATE
,CN.CLIENT_NAME
,ADDRESS.STATE AS CLIENT_STATE
,A.AGENCY_NAME
,A.AGENCY_STATE
,F.BROKER_CONT AS BROKER_CONTACT
,Sum(PD.BILLED_PREMIUM) AS PREMIUM
,PREMIUM_LOB_MAP.DESCRIPTION AS LINE_OF_BUS
,MAP_REGION_OFFICE.DATABASE_NAME
FROM OFFICE_FILTER
,POLICY AS P
,PREMIUM_DETAIL AS PD
,CLIENT_NAMES AS CN
,AGENCY AS A
,FMFC_DETAIL AS F
,EDIT_LONG_CODE AS EL
,MAP_REGION_OFFICE
,PREMIUM_LOB_MAP
,EDIT_SHORT_CODE
,EDIT_SHORT_CODE AS EDIT_SHORT_CODE_1
,BROKER_FILTER
,LOB_FILTER
,UNDERWRITER_FILTER
,ADDRESS
,EDIT_LONG_CODE
WHERE 1 = 1
and (P.POLICY_NUMBER = PD.POLICY_NUMBER)
AND (P.POLICY_DATE_TIME = PD.POLICY_DATE_TIME)
and P.CLIENT_NUMBER = CN.CLIENT_NUMBER
and P.AGENCY_ID = A.AGENCY_ID
and (P.POLICY_NUMBER = F.POLICY_NUMBER)
AND (P.POLICY_DATE_TIME = F.POLICY_DATE_TIME)
and F.UNDERWRITER = EL.CODE
and F.UND_OFFICE = MAP_REGION_OFFICE.OLD_UND_OFFICE
and PD.PREMIUM_LOB = PREMIUM_LOB_MAP.PREMIUM_LOB
and P.BILLED_PREMIUM_ID = EDIT_SHORT_CODE.CODE
and P.NEW_OR_RENEWAL = EDIT_SHORT_CODE_1.CODE
and A.AGENCY_NUMBER = BROKER_FILTER.AGENCY_NUMBER
and PREMIUM_LOB_MAP.PREMIUM_LOB = LOB_FILTER.PREMIUM_LOB
and EL.CODE = UNDERWRITER_FILTER.UW_CODE
and OFFICE_FILTER.OFFICE_CODE = MAP_REGION_OFFICE.UND_OFFICE
and F.RTNG_STATE = EDIT_LONG_CODE.CODE
and (((LOB_FILTER****N_CODE) = [ Forms ] ! [ frmMain ] ! [txtSession ])
AND ((UNDERWRITER_FILTER****N_CODE) = [ Forms ] ! [ frmMain ] ! [ txtSession ])
AND ((OFFICE_FILTER****N_CODE) = [ Forms ] ! [ frmMain ] ! [ txtSession ])
AND ((BROKER_FILTER****N_CODE) = [ Forms ] ! [ frmMain ] ! [ txtSession ])
AND ((EL.NAME) = 'UNDERWRITER')
AND ((EL.TBNAME) = 'FMFC_DETAIL')
AND ((P.BILLED_PREMIUM_ID) In ('5', '3', '2', '1'))
AND ((F.UND_OFFICE) <> 'V02')
AND ((EDIT_SHORT_CODE.NAME) = "BILLED_PREMIUM_ID")
AND ((EDIT_SHORT_CODE.TBNAME) = "POLICY")
AND ((EDIT_SHORT_CODE_1.NAME) = "NEW_OR_RENEWAL")
AND ((EDIT_SHORT_CODE_1.TBNAME) = "POLICY")
AND ((MAP_REGION_OFFICE.DATABASE_NAME) = "FMFCRPT")
AND ((ADDRESS.SEQUENCE_NUMBER) = 1)
AND ((EDIT_LONG_CODE.NAME) = "RTNG_STATE")
AND ((EDIT_LONG_CODE.TBNAME) = "FMFC_DETAIL")
AND ((PREMIUM_LOB_MAP.BUS_UNIT) Not In ('PL', 'PROF', 'MGMT')))
GROUP BY MAP_REGION_OFFICE.REGION_NAME
,MAP_REGION_OFFICE.UND_OFFICE_NAME
,EL.DESCRIPTION
,PREMIUM_LOB_MAP.PRODUCT
,P.POL_ALT_ID
,P.POLICY_NUMBER
,P.POL_EFF_DATE
,P.POL_EXP_DATE
,P.CHANGE_EFF_DATE
,P.POLICY_DATE_TIME
,EDIT_SHORT_CODE.DESCRIPTION
,EDIT_SHORT_CODE_1.DESCRIPTION
,EDIT_LONG_CODE.DESCRIPTION
,CN.CLIENT_NAME
,ADDRESS.STATE
,A.AGENCY_NAME
,A.AGENCY_STATE
,F.BROKER_CONT
,PREMIUM_LOB_MAP.DESCRIPTION
,MAP_REGION_OFFICE.DATABASE_NAME
ORDER BY MAP_REGION_OFFICE.UND_OFFICE_NAME
,EL.DESCRIPTION
,PREMIUM_LOB_MAP.PRODUCT
,P.POL_ALT_ID
,P.POLICY_DATE_TIME;

r937 · #2 (**permalink**) 01-23-12, 11:45

Quote:

Originally Posted by fsher

... putting the joins in the where clause seems so much more straight forward and clearer

and so much more totally wrong for outer joins

i'm not sure what you're asking -- do you want us to reformat that msaccess query?

we all know that the parentheses that msaccess insists on are worthy of contempt and ridicule

are you asking to see the ANSI SQL version of that query?

just take off the parentheses

r937 · #3 (**permalink**) 01-23-12, 12:08

here you go... rewritten to be more understandable

notice that each join refers to a table higher up, i.e. previously mentioned, in the FROM clause

now the actual table relationships and database design start to show through

~far~ more understandable to separate the join conditions logically by tables being joined, and the best place for this is in the ON clauses

Code:

  FROM POLICY AS P 
INNER 
  JOIN EDIT_SHORT_CODE 
    ON EDIT_SHORT_CODE.CODE = P.BILLED_PREMIUM_ID
   AND EDIT_SHORT_CODE.NAME = "BILLED_PREMIUM_ID" 
   AND EDIT_SHORT_CODE.TBNAME = "POLICY" 
INNER 
  JOIN EDIT_SHORT_CODE AS EDIT_SHORT_CODE_1 
    ON EDIT_SHORT_CODE_1.CODE = P.NEW_OR_RENEWAL
   AND EDIT_SHORT_CODE_1.NAME = "NEW_OR_RENEWAL" 
   AND EDIT_SHORT_CODE_1.TBNAME = "POLICY" 
INNER 
  JOIN CLIENT_NAMES AS CN 
    ON CN.CLIENT_NUMBER = P.CLIENT_NUMBER 
INNER 
  JOIN ADDRESS 
    ON ADDRESS.CLIENT_NUMBER = CN.CLIENT_NUMBER
   AND ADDRESS.SEQUENCE_NUMBER = 1 
INNER 
  JOIN PREMIUM_DETAIL AS PD
    ON PD.POLICY_NUMBER     = P.POLICY_NUMBER 
   AND PD.POLICY_DATE_TIME  = P.POLICY_DATE_TIME
INNER 
  JOIN PREMIUM_LOB_MAP 
    ON PREMIUM_LOB_MAP.PREMIUM_LOB = PD.PREMIUM_LOB
   AND PREMIUM_LOB_MAP.BUS_UNIT NOT IN ( 'PL', 'PROF', 'MGMT' )
INNER 
  JOIN LOB_FILTER 
    ON LOB_FILTER.PREMIUM_LOB = PREMIUM_LOB_MAP.PREMIUM_LOB
   AND LOB_FILTER****N_CODE = [ Forms ] ! [ frmMain ] ! [txtSession ]  
INNER 
  JOIN AGENCY AS A 
    ON A.AGENCY_ID = P.AGENCY_ID 
INNER 
  JOIN BROKER_FILTER 
    ON BROKER_FILTER.AGENCY_NUMBER = A.AGENCY_NUMBER
   AND BROKER_FILTER****N_CODE = [ Forms ] ! [ frmMain ] ! [ txtSession ]   
INNER 
  JOIN FMFC_DETAIL AS F
    ON F.POLICY_NUMBER = P.POLICY_NUMBER 
   AND F.POLICY_DATE_TIME = P.POLICY_DATE_TIME 
   AND F.UND_OFFICE <> 'V02' 
INNER 
  JOIN EDIT_LONG_CODE
    ON EDIT_LONG_CODE.CODE = F.RTNG_STATE
   AND EDIT_LONG_CODE.NAME = "RTNG_STATE" 
   AND EDIT_LONG_CODE.TBNAME = "FMFC_DETAIL" 
INNER 
  JOIN EDIT_LONG_CODE AS EL 
    ON EL.CODE = F.UNDERWRITER
   AND EL.NAME = 'UNDERWRITER' 
   AND EL.TBNAME = 'FMFC_DETAIL' 
INNER 
  JOIN MAP_REGION_OFFICE 
    ON MAP_REGION_OFFICE.OLD_UND_OFFICE = F.UND_OFFICE
   AND MAP_REGION_OFFICE.DATABASE_NAME = "FMFCRPT" 
INNER
  JOIN OFFICE_FILTER     
    ON OFFICE_FILTER.OFFICE_CODE = MAP_REGION_OFFICE.UND_OFFICE
   AND OFFICE_FILTER****N_CODE = [ Forms ] ! [ frmMain ] ! [ txtSession ] 
INNER 
  JOIN UNDERWRITER_FILTER 
    ON UNDERWRITER_FILTER.UW_CODE = EL.CODE
   AND UNDERWRITER_FILTER****N_CODE = [ Forms ] ! [ frmMain ] ! [ txtSession ] 
 WHERE P.BILLED_PREMIUM_ID IN ( '5', '3', '2', '1' )

futurity · #4 (**permalink**) 01-24-12, 11:07

The biggest problem with the statement you've posted is the complete and utter lack of formatting and the absurd inundation of pointless parentheses. Any attempt to format that disaster will improve clarity.

However, just to lend a word of support to r937 (not that he needs it!), ansi joins are the only way to do non-inner joins, so if you ever intend to use those features of SQL, you might as well become comfortable using them.

I also find, both in formulating my SQL and in being able to understand it afterwards, that putting join criteria in the ON clauses and filtering criteria in the WHERE clauses helps immensely, which is a little different than what r937 has done (putting everything in the ON clauses). Yes, this becomes a purely semantic distinction, as the same thing is accomplished either way, so whatever works best for you.

Spend five minutes cleaning up that pile of manure and you'll realize just how fubar Micros**t's "SQL dialect" is.

Code:

FROM
    POLICY AS P
    INNER JOIN PREMIUM_DETAIL AS PD
        ON P.POLICY_NUMBER = PD.POLICY_NUMBER
        AND P.POLICY_DATE_TIME = PD.POLICY_DATE_TIME
    INNER JOIN CLIENT_NAMES AS CN
        ON P.CLIENT_NUMBER = CN.CLIENT_NUMBER
    INNER JOIN AGENCY AS A
        ON P.AGENCY_ID = A.AGENCY_ID
    INNER JOIN FMFC_DETAIL AS F
        ON P.POLICY_NUMBER = F.POLICY_NUMBER
        AND P.POLICY_DATE_TIME = F.POLICY_DATE_TIME
    INNER JOIN EDIT_LONG_CODE AS EL
        ON F.UNDERWRITER = EL.CODE
    INNER JOIN MAP_REGION_OFFICE
        ON F.UND_OFFICE = MAP_REGION_OFFICE.OLD_UND_OFFICE
    INNER JOIN PREMIUM_LOB_MAP
        ON PD.PREMIUM_LOB = PREMIUM_LOB_MAP.PREMIUM_LOB
    INNER JOIN EDIT_SHORT_CODE
        ON P.BILLED_PREMIUM_ID = EDIT_SHORT_CODE.CODE
    INNER JOIN EDIT_SHORT_CODE AS EDIT_SHORT_CODE_1
        ON P.NEW_OR_RENEWAL = EDIT_SHORT_CODE_1.CODE
    INNER JOIN BROKER_FILTER
        ON A.AGENCY_NUMBER = BROKER_FILTER.AGENCY_NUMBER
    INNER JOIN LOB_FILTER
        ON PREMIUM_LOB_MAP.PREMIUM_LOB = LOB_FILTER.PREMIUM_LOB
    INNER JOIN UNDERWRITER_FILTER
        ON EL.CODE = UNDERWRITER_FILTER.UW_CODE
    INNER JOIN OFFICE_FILTER
        ON OFFICE_FILTER.OFFICE_CODE = MAP_REGION_OFFICE.UND_OFFICE
    INNER JOIN ADDRESS
        ON CN.CLIENT_NUMBER = ADDRESS.CLIENT_NUMBER
    INNER JOIN EDIT_LONG_CODE
        ON F.RTNG_STATE = EDIT_LONG_CODE.CODE
WHERE
    LOB_FILTER****N_CODE = [ Forms ] ! [ frmMain ] ! [txtSession ]
    AND UNDERWRITER_FILTER****N_CODE = [ Forms ] ! [ frmMain ] ! [ txtSession ]
    AND OFFICE_FILTER****N_CODE = [ Forms ] ! [ frmMain ] ! [ txtSession ]
    AND BROKER_FILTER****N_CODE = [ Forms ] ! [ frmMain ] ! [ txtSession ]
    AND EL.NAME = 'UNDERWRITER'
    AND EL.TBNAME = 'FMFC_DETAIL'
    AND P.BILLED_PREMIUM_ID In ('5', '3', '2', '1')
    AND F.UND_OFFICE <> 'V02'
    AND EDIT_SHORT_CODE.NAME = "BILLED_PREMIUM_ID"
    AND EDIT_SHORT_CODE.TBNAME = "POLICY"
    AND EDIT_SHORT_CODE_1.NAME = "NEW_OR_RENEWAL"
    AND EDIT_SHORT_CODE_1.TBNAME = "POLICY"
    AND MAP_REGION_OFFICE.DATABASE_NAME = "FMFCRPT"
    AND ADDRESS.SEQUENCE_NUMBER = 1
    AND EDIT_LONG_CODE.NAME = "RTNG_STATE"
    AND EDIT_LONG_CODE.TBNAME = "FMFC_DETAIL"
    AND PREMIUM_LOB_MAP.BUS_UNIT Not In ('PL', 'PROF', 'MGMT')

tonkuma · #5 (**permalink**) 02-01-12, 01:55

One of advantages of ANSI SQL join syntax is that it defines (logical) sequence of joins without using parentheses.

For example:

Code:

(a)
SELECT ... FROM ( ( a JOIN b ON <cond-x> ) JOIN c ON <cond-y> )

(b)
SELECT ... FROM ( a JOIN ( b JOIN c ON <cond-y> ) ON <cond-x> )

Even if removed parentheses, position of ON conditions specify the join sequence.

Code:

(a)
SELECT ... FROM a JOIN b ON <cond-x> JOIN c ON <cond-y>

(b)
SELECT ... FROM a JOIN b JOIN c ON <cond-y> ON <cond-x>

I prefer the formatting of this way.

Code:

(a)
SELECT ... 
 FROM  a
 JOIN
       b
   ON  <cond-x>
 JOIN
       c
   ON  <cond-y>

(b)
SELECT ...
 FROM  a
 JOIN
       b
 JOIN
       c
   ON  <cond-y>
   ON  <cond-x>

Logical join sequence influences the result of joins including outer join.

An example of different result of different join sequence.
Test data.

Code:

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM  a
;
------------------------------------------------------------------------------

COL_A      
-----------
          4
          5
          6
          7

  4 record(s) selected.

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM  b
;
------------------------------------------------------------------------------

COL_B      
-----------
          2
          3
          6
          7

  4 record(s) selected.

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM  c
;
------------------------------------------------------------------------------

COL_C      
-----------
          1
          3
          5
          7

  4 record(s) selected.

( ( a join b ) join c )

Code:

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM
       a
 LEFT  OUTER JOIN
       b
   ON  col_b = col_a
 INNER JOIN
       c
   ON  col_c = col_b
;
------------------------------------------------------------------------------

COL_A       COL_B       COL_C      
----------- ----------- -----------
          7           7           7

  1 record(s) selected.

( a join ( b join c ) )

Code:

------------------------------ Commands Entered ------------------------------
SELECT *
 FROM
       a
 LEFT  OUTER JOIN
       b
 INNER JOIN
       c
   ON  col_c = col_b
   ON  col_b = col_a
;
------------------------------------------------------------------------------

COL_A       COL_B       COL_C      
----------- ----------- -----------
          7           7           7
          6           -           -
          4           -           -
          5           -           -

  4 record(s) selected.

tonkuma · #6 (**permalink**) 02-01-12, 07:05

I prefer to format this way.

step 1: Put left table and right table row by row, like...

Code:

 FROM
       <left-table>
       <right-table>

step 2: Add JOIN method, like...

Code:

 FROM
       <left-table>
 INNER JOIN
 LEFT  OUTER JOIN
 RIGHT OUTER JOIN
 CROSS JOIN
       <right-table>

step 3: Add ON conditions, like...

Code:

 FROM
       <left-table>
 LEFT  OUTER JOIN
       <right-table>
   ON  <predicate-x>
   AND NOT
       <predicate-y>
   OR  <predicate-z>
   AND
     (
           <predicate-p>
       AND <predicate-q>
       OR  <predicate-r>
     )
   OR  NOT
       <predicate-s>

tonkuma · #7 (**permalink**) 02-03-12, 15:10

Quote:

Originally Posted by tonkuma

One of advantages of ANSI SQL join syntax is that it defines (logical) sequence of joins without using parentheses.

For example:

Code:

(a)
SELECT ... FROM ( ( a JOIN b ON <cond-x> ) JOIN c ON <cond-y> )

(b)
SELECT ... FROM ( a JOIN ( b JOIN c ON <cond-y> ) ON <cond-x> )

Even if removed parentheses, position of ON conditions specify the join sequence.

Code:

(a)
SELECT ... FROM a JOIN b ON <cond-x> JOIN c ON <cond-y>

(b)
SELECT ... FROM a JOIN b JOIN c ON <cond-y> ON <cond-x>

...
...

If considered that ON clause actually specify join execution,
the sequence of elements in ANSI join syntax looks like reverse Polish notation.
RPN is known on which parentheses are not necessary for binary operations.
Join operations are always done between two tables including result table of another join operation.
So, it is understandable that ANSI join syntax strictly specify join sequence without parentheses.

Japanese/Korean sentences also look like RPN, which are
S + O + V.
different from Englich(may be Chinese too)
S + V + O.

Internet Services

Web Development

Digital Marketing

Consumer Tech