Results 1 to 8 of 8

Thread: Why inner join?

  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: Why inner join?

    What's difference between these two queries? (Noobest question ever huh?! )

    Code:
    SELECT * FROM
    Table1, Table2
    WHERE Table1.id = Table2.id
    Code:
    SELECT * FROM
    TABLE1
    INNER JOIN
    TABLE2
    ON Table1.id = Table2.id

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in performance and results produced, there is no difference

    in clarity and maintainability, the explicit JOIN syntax is ~way~ better

    you've posted a trivially simple example, just wait'll you see something way more complex written with comma joins, you will silently want to kill the guy who wrote it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    A slightly different take along the same lines as what Rudy posted... When I see implicit "comma" syntax I assume the author intends to create a Cartesian product. If they didn't, I would expect them to have explicitly defined the relationship as an INNER JOIN. On top of the former being just plain messier and more difficult to maintain, it isn't as meaningful as the INNER JOIN. There is no mistaking intent with the latter option. When working as part of a team it is important to be clear and concise about the intent of one's solutions.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's an example of comma joins...
    Code:
    select br.BUSN_ROLE_NM "Administration",
    br.BUSN_ROLE_CD "Business role code",
    br.BUSN_ROLE_TYP_CD "Type of role",
    br.BUSN_ROLE_VOID_IND "Void index",
    e.EMPL_ID "Emloyee Id",
    e.EMPL_DRV_NM "Name",
    sr1.SEC_ROLE_NM "Security Role",
    sra.sec_role_auth_max "Emp Claim Level",
    b.EMPL_DRV_NM "Supervisor",
    auth_typ_cd
    from employee e,
    employee b,
    employee_relationship er,
    business_role br,
    business_security_role bsr,
    security_role sr1,
    employee_business_role ebr,
    security_role_authorization sra
    where
    e.EMPL_ID = ebr.EMPL_ID
    and e.EMPL_ID = er.EMPL_ID
    and b.EMPL_ID = er.EMPL_ID_RLT
    and bsr.SEC_ROLE_CD = ebr.SEC_ROLE_CD
    and ebr.SEC_ROLE_CD = sr1.SEC_ROLE_CD
    and br.busn_role_cd = bsr.BUSN_ROLE_CD
    and br.busn_role_cd = ebr.BUSN_ROLE_CD
    and sra.SEC_ROLE_CD = bsr.SEC_ROLE_CD
    and sra.SEC_ROLE_CD = ebr.SEC_ROLE_CD
    and sra.SEC_ROLE_CD = sr1.SEC_ROLE_CD
    order by 6,7
    this query does not work correctly

    your task is to find out why

    hint: incorrect join condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Good thing this query wasn't joined at the hip.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937 View Post
    here's an example of comma joins...
    Of course, TRWTF was the use of superfluous aliases....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28

    Both are same for Query Optimizer

    SQL Server optimizer treat both queries are same as attachment,you can check the estimated actual execution plan of the query,you can improve the performance with use of query hint

    OPTION (MERGE | LOOP | HASH JOIN)

    For Further Details
    SQL Server : Query Hints A|U|R|E|U|S – S|A|L|A|H
    Attached Thumbnails Attached Thumbnails 1.jpg   2.jpg   3.jpg  

  8. #8
    Join Date
    Mar 2008
    Posts
    40
    Guys I truly appreciate all your helps. You all are the overachievers

Posting Permissions

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