Results 1 to 4 of 4

Thread: QUery

  1. #1
    Join Date
    Oct 2003
    Posts
    83

    Post Unanswered: QUery

    For retreiving a particular result I can write a query in this way-
    Select t1.person_id,t2.age from table1 t1,table2 t2
    where t1.person_id=t2.person_id

    Or
    I can write the same query in a different manner like this-
    Select t1.person_id,t2.age from table1 t1
    inner join table2 t2
    on t1.person_id=t2.person_id

    I want to know is there any particular effect between these two processes?Which one is supposed to follow?

    Subhasish

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    There is no difference in effects for the both, but the 'inner join' statement complies with the latest ANSI standard and the other one is the old way.
    Johan

  3. #3
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    The second version is the ANSI way of writing a join. It's also far easier to understand IMHO.

    To make that query even simpler to follow, you could use a USING clause:

    Code:
    SELECT
        t1.person_id
      , t2.age
    FROM
        table1 t1
    INNER JOIN
        table2 t2
        USING ( person_id )
    Have a look at this ANSI join article at Database Journal.

    Hope this helps,

    Matt.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    subhasishray,

    the reason it make no difference for a simple query such as yours is because MSSQL Server will change WHERE logic to JOIN logic prior to processing whenever possible. For complex queries the optimizer may not be able to do this, and the WHERE logic can affect performance.

    use JOIN logic whenever you can. it logically separates table linking from query criteria and filters.

    blindman

Posting Permissions

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