Joining tables, Return Primary or Foreign key? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Joining tables, Return Primary or Foreign key?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-05 : 07:19:45

Rob writes "I was wondering if there is any performance difference between choosing whether to return the Primary key or the foreign key in a select statement when joining tables. For example I have three tables I need to return data from the first table and the third table but not really the middle table other than maybe its primary key. Since Nothing else is being returned from the middle table would it be faster to return the foreign key from the first table? or make no difference what so ever. I thought maybe the primary key would be faster as it is indexed but then nothing else is being return from that table If there is no difference then is there some standard practise like always choose the Primary key or Vice versa.
Example:
<Fault>-<Simulator>-<SimType>

Many thanks if you could help clear this up for me."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 07:57:09

There should be no difference for INNER JOIN's, but I always try to return keys from "primary" tables in queries. That way, when you do use OUTER JOIN's you will not end up with Null's.

But then again, I'm VERY picky about my SQL statements.

(I NEVER use FULL or RIGHT outer joins, for example. NEVER. )

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-05 : 07:58:52

You should. FULL JOINs are a great way to find mismatched rows in both tables with one query.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 08:27:42

UNION is much better for that ...

The result of FULL OUTER JOIN's -- if this type of join is needed -- will always return NULLS in the resultset's key columns. To avoid this, you need to say

INSULL(a.pk, b.pk) as pk

and so on, completely turning your primary key into a formula !

FULL OUTER JOINS should always be replaced by 1 of 2 methods:

1. A UNION/GROUP BY solution

to find mismatched rows, or to ensure you have all data from two tables without missing anything (classic case: budget vs. actual, sometimes you have a budget with no actual, sometimes vice versa) :

SELECT CustomerID, sum(budget), sum(actual)
from
(
select customerID, budget, 0 as actual
from
Budgets
UNION ALL
select customerID, 0 as budget, actual
from
Actuals
) b
group by customerID

or to find mismatched rows there is nothing more efficent than:

select f1, f2 , ...
from
(select * from table1 union all select * from table2)
a
group by f1,f2 ...
having count(*) <> 2

(that guy has been posted MANY times here in the forums -- and there is no issue with NULLS that won't join. above is not the best way to solve the problem, just a simplified example)

2. A CROSS JOIN / LEFT OUTER JOIN solution.

this is a little tricky to explain, maybe I will later in the day if I get a chance to post ... but basically, to ensure all rows are returned, you say:


(A x B x C) -> D
            -> E

x = cross join
-> = left outer join

where A,B,C are the "master tables" containing 1 row per primary key (i.e., table of Offices, table of Customer Types, table of Contract Types) and in tables D and E you have the metrics, grouped by the 3 key fields (offices, customer types, contract types).

This ensures that no matter what is in tables D and E, you return 1 row for all combinations and no data is ever dropped out .

Then, typically in your WHERE clause, you say (WHERE D.pk is not Null OR E.Pk is not null).

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources