order of tables in a join

Panoy · #1 (**permalink**) 03-03-09, 05:20

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!

pootle flump · #2 (**permalink**) 03-03-09, 05:30

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

r937 · #3 (**permalink**) 03-03-09, 06:04

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

Panoy · #4 (**permalink**) 03-11-09, 03:03

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!

pootle flump · #5 (**permalink**) 03-11-09, 03:58

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.

Panoy · #6 (**permalink**) 03-11-09, 04:48

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech