Proper way to join tables
-
I am in the middle of an argument with the development manager about the proper way to join tables.
I say when one joins two tables the second table comes after the first table in the join. Like this
Select company.name, customer.name
from company
join customer on company.custid = customer.custid
The developemnt manager says:
Select company.name, customer.name
from company
join customer on customer.custid = company.custid
Can someone please clear this up?
Is there some documentation that show the correct way.
If I am wrong then I buy lunch for the dev team. If the manager is wrong he buys lunch for me and his devlopers.
Thanks in advance.
Gary
-
I dont think it matters... does it
its not like we are using oracle 7.3 or something... unless we set some rather technical options. In which case it is likely to depend more on the selectivity of the tables rather than anything else?
-
I'll go with the doesn't matter part (assuming sql server 2k +... and maybe even 7 but I never worked with them).
It's the server that takes the fastest possible way to access the data (assuming the server is well tuned). The only time this isn't true is when you use join hints which is not recommended unless you really have no other choice.
-
I'm in the Doesn't matter school of thought.
The t-sql, or ansi-sql standard does not state that there is an order requirement, nor have I seen a recommendation of the order.
It comes down to a couple of things which are subjective.
Create a corporate standard which enables each db user to write queries in a standard way that makes queries readable to all others.
I don't think the Optimizer cares. I have tried to reverse the items to see if I had a different query plan, and it had no effect.
Last, Your manager has the ability to fire your ass, so you may want to let him "Win".
-
"Last, Your manager has the ability to fire your ass, so you may want to let him "Win"."
Now that's something I don't like to do... I'd try calling it a draw
. -
I'm in the does not matter camp as well. But my personal style is the same as yours...
I wasn't born stupid - I had to study.
-
As far as SQL is concerned IT doesn't care. HOWEVER,
As a general rule IF you start with the SMALLEST table and work outward your queries will run better. This will start with the smallest recordset and apply the filtering on that.
So in essence you are both right/wrong.
Roll over. This battle you can't win. Later on start a new battle that you can win....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
The ANSI standard is your method but for the most part it is a case of personal preference.
My personal preference is to keep the WHERE clause for filtering and the JOIN clause for relating tables.
It makes the query much easier to read and therefore debug.
I also prefer to specify INNER JOIN so that the meaning is absolutely unambiguous.
-
I have been using constraints within the JOIN [ ON( TablA.[ID] = TableB.[ID] AND TableB.[Description] = 'A') ] working under the assumption that the optimzer will handle this better.
Am I working under a false assumption? (generally I only use Indexed fields for this approach, but that is not a hard rule for me...).
I wasn't born stupid - I had to study.
-
Funny this is something like saying I'd rather write 1 = 1 than 1 = 1
If you are concerned about execution it does not matter. If is about style I prefer yours!
This discussions remind me about many religious ones like Identity Columns, Surrogate Keys, TableName in plurals... etc
* Noel -
I've spent 1 month tuning 400 queries that use a variety of methods for joining tables so from a performance point of view it makes little, if any difference whether you use the JOIN or the WHERE clause.
But readability is all.
Some of the queries I have had to deal with print out on 20 pages of A4 and trying to work out what is going on in a query that size is not fun.
If you have a coding standard that insists on putting all the join instructions in the WHERE clause then at least put the joins at the top and the filters at the bottom.
Personally I find that
FROM A LEFT JOIN B is more obvious than
A.id=* B.id
-
So it looks like the consensus is:
select customer.name, company.name
from company
inner join customer on company.custid = customer.custid
where company.name like 'special company'
The reason for the question is, we too are going through all the stored procedures, views, etc... and establishing a standard. This is something the previous folks did not do.
This is going to be an ongoing process so thanks for all the responses.
I guess I will have to buy my own lunch, and as a way to start this process on the right track, I will buy the managers lunch as well, this one time.
Gary
-
Not so fast. If your question is specifically about the order of tables in the ON clause, everyone who expressed a preference said they agreed with your style. So do I. He owes you lunch.
The suggestion to use "INNER JOIN" instead of just "JOIN" is a good one if you're establishing standards, but in your original post both you and the boss used JOIN. So unless you can track David Poole down and buy him lunch for being the first to point it out, it has no bearing on your wager. Maybe you can email him a picture of the lunch you get.
-
There is a fixed table position for OUTER JOIN in ANSI SQL, the text below is from chapter five of SQL Performance Tuning By Peter Gulutzan and Trudy Pelzer published 2003. So there is no winner.
(An outer join can be much slower than an inner join because Table1 must be the outer table and Table2 must be the inner table—otherwise, the join won't work. So even if Table1 is smaller and better indexed and has a restrictive expression, it still can't be the inner table. And that means the DBMS can't pick the optimum join plan.)
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply