Basic SQL Query Select Question FK tables

Hello all,

Okay this is a basic question but want the best practice.   If I have a FK ID field in my main query FROM table that joins to that PK table.    I know it does not matter but best practice the ID field should that reference the main FROM table or the joined PF table.   For example,

SELECT
IV.InvoiceGroupID,   --should this use the main table InvoiceGroupID or the IG table best practice
IG.InvoiceGroupName
FROM Invoice IV
JOIN InvoiceGroup IG ON IV. InvoiceGroupID = IG.InvoiceGroupID

Also one other thing is the JOIN which table should always be the first in the ON statement?   Sometimes I find myself mixing them.

Again all best practice I am looking for.

Thanks all
sbornstein2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
Since the JOIN..ON query forces these two be the same value, and both columns are available when the SELECT clause processes, I don't believe it matters.

>Also one other thing is the JOIN which table should always be the first in the ON statement?  
I haven't heard of an instance where this matters, but I almost always see JOIN ... ON left side object.column = right side object.column.
David ToddSenior Database AdministratorCommented:
Hi

Here's a suggestion for performance on Foreign key joins: Consider adding a non-clustered index on the foreign key column. The other side of the join is usually a primary key, which is likely to be the clustered index of that table. Primary key constraints are implemented by an index. Foreign key constraints are not automatically indexed.

HTH
  David
Scott PletcherSenior DBACommented:
If the query can be limited to a single table, don't reference the other table in the query.  For example, instead of:

SELECT
IV.InvoiceGroupID,   --should this use the main table InvoiceGroupID or the IG table best practice
IG.InvoiceGroupName
FROM Invoice IV
JOIN InvoiceGroup IG ON IV. InvoiceGroupID = IG.InvoiceGroupID

You'd write:
SELECT
IG.InvoiceGroupID,   --should this use the main table InvoiceGroupID or the IG table best practice
IG.InvoiceGroupName
FROM InvoiceGroup IG
so that SQL never has to reference the other table.

If you actually do use columns from both tables in the query, I don't think it matters which one you use.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
sbornstein2, do you still need help with this question?
sbornstein2Author Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.