Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Question Unanswered: order of tables in a join

    Hi to all,

    Beginner question here...

    Does the order of precedence of the tables matter if you are joining a number of tables together?

    Lets take for example:

    I have 3 tables to join:

    Table 1 is "members" table
    Table 2 is "companies" table
    Table 3 is "units" table

    "members" table contains the foreign key field of "companies" table which is named "company_id", it also contains the foreign key field of the "units" table and is named "unit_id".

    I would join "companies" table and "units" table in order to retrieve their "company name" field and "unit name" field respectively.

    This is my query:

    Code:
    SELECT 
       member_id, 
       surname, 
       first_name, 
       middle_name, 
       company_name, 
       unit_name, 
       year_hired
    FROM members
    INNER JOIN companies ON members.company_id = companies.company_id
    INNER JOIN units ON members.unit_id = units.unit_id;
    What if I rewrite it to this query?

    Code:
    SELECT
       member_id,
       surname,
       first_name,
       middle_name,
       company_name,
       unit_name,
       year_hired
    FROM units
    INNER JOIN members ON units.unit_id = members.unit_id
    INNER JOIN companies ON companies.company_id = members.company_id;

    1.) Is this query also the same as the first query if I have change the order of the tables, I mean, will that produce the same result as my very first query?

    2.) Is there a speed difference if I change the order of the tables?

    3.) How about the order of the fields? Example if this is the join clause:

    Code:
    INNER JOIN members ON units.unit_id = members.unit_id
    Is this also the same if you would rewrite it as:
    Code:
    	INNER JOIN members ON members.unit_id = units.unit_id
    I just switch the position of the "members.unit_id" field and "units.unit_id" field that are used for equality checking

    Btw, I have also read some articles in the net that if you are using OUTER JOINS, then the precedence of the tables in your query does matter.


    many thanks!
    Programming is fun!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SQL is a declarative language - you say what result you want and the engine figures out how to do it. As such, logically identical queries should, in theory at least, result in exactly the same physical processes (where all other things are constant, such as RDBMS, hardware, load etc).

    So in order:
    1) Yes

    2.) No

    3.) No

    Note that table order matters if you are using left or right outer joins (you should always use left, BTW). Also, many people have conventions they use. For example, I always list the second table first in my join predicate e.g.
    Code:
    ....tableA
    INNER JOIN tableB
    ON tableB.col1 = tableA.col1
    AND tableB.col2 = tableA.col2
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with indentation, pootle's last point is even stronger --
    Code:
      FROM tableA
    INNER 
      JOIN tableB
        ON tableB.col1 = tableA.col1
       AND tableB.col2 = tableA.col2
    notice how the table being joined (tableB) has all the join conditions (the ON clause) listed immediately below it, thus making it super clear ~how~ the table is being joined to the previously-mentioned table(s)

    the columns on the left side of the ON conditions line up nicely with the table name, while the columns that stick out on the right side always refer to tables further up in the code

    super clear == more understandable == easier comprehension == faster maintanance

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2007
    Posts
    77
    Quote Originally Posted by Panoy

    3.) How about the order of the fields? Example if this is the join clause:

    Code:
    INNER JOIN members ON units.unit_id = members.unit_id
    Is this also the same if you would rewrite it as:
    Code:
    	INNER JOIN members ON members.unit_id = units.unit_id
    I just switch the position of the "members.unit_id" field and "units.unit_id" field that are used for equality checking
    Quote Originally Posted by pootle flump
    SQL is a declarative language - you say what result you want and the engine figures out how to do it. As such, logically identical queries should, in theory at least, result in exactly the same physical processes (where all other things are constant, such as RDBMS, hardware, load etc).

    So in order:
    1) Yes

    2.) No

    3.) No
    I would like to clarify question/answer number 3, in what way they are different? I thought they are just the same, and the position of the fields were just interchanged.

    many thanks!
    Programming is fun!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    They are the same. The first part of your question read as though you were saying "Is there a difference?". I notice now you later say "Are these the same?", hence the confusion.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2007
    Posts
    77
    Quote Originally Posted by pootle flump
    They are the same. The first part of your question read as though you were saying "Is there a difference?". I notice now you later say "Are these the same?", hence the confusion.

    many thanks!
    Programming is fun!

Posting Permissions

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