Results 1 to 7 of 7

Thread: ANSI Joins

  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: ANSI Joins

    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;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fsher View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2008
    Posts
    277
    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')

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 02-07-12 at 09:40. Reason: Add line breaks in sample codes.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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>

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    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.
    Last edited by tonkuma; 02-03-12 at 16:24.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •