Basic SQL Query Select Question FK tables

sbornstein2
sbornstein2 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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 Administrator

Commented:
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
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
sbornstein2, do you still need help with this question?

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial