|
|
Posted by Hugo Kornelis on 02/16/06 00:11
On Wed, 15 Feb 2006 12:41:09 GMT, Brian Wotherspoon wrote:
>
>
>I have a table with data that is refreshed regularly but I still need to
>store the old data. I have created a seperate table with a foreign key
>to the table and the date on which it was replaced. I'm looking for an
>efficient way to select only the active data.
>
>Currently I use:
>
>SELECT ...
>FROM DataTable AS D
>LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
>WHERE D.Key IS NULL
>
>However I am not convinced that this is the most efficient, or the most
>intuitive method of acheiving this.
>
>Can anyone suggest a more efficient way of getting this information
>please.
>
>Many thanks.
Hi Brian,
The most intuitive way, IMO, is
SELECT ...
FROM DataTable AS D
WHERE NOT EXISTS
(SELECT *
FROM InActiveTable AS I
WHERE I.Key = D.Key)
The most efficient is either the above or your own LEFT OUTER JOIN query
(but do change D.Key to I.Key in the IS NOT NULL check!!) - but it'll be
only efficient if the I.Key column is indexed.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|