Query same table multiple times

Question

I have always used multiple joins when I need to reference same data on different columns. Like this:

select
    a.ContactName
    ,b.ContactName
    ,c.ContactName
from
    OrderBase as o
    left join ContactBase as a on o.contactid1 = a.ContactId
    left join ContactBase as b on o.contactid2 = b.ContactId
    left join ContactBase as c on o.contactid3 = c.ContactId

I have always thought this was very inefficient. Loading the table 3 times for 3 lookups. Are there a more efficient way to do this? I don't mean the writing of code, but the use of resources. Are there any good tools implemented in the later versions of SQL server that addresses this?

Answer 1

The first comment is that storing the same value is multiple columns is often a suspicious pattern. Often, such a data structure is better served using a junction table with one row per "OrderBase" and one per "ContactId". A junction table is not always the best solution, but it often is.

As for your query:

select a.ContactName, b.ContactName, c.ContactName
from OrderBase as o left join
     ContactBase a
     on o.contactid1 = a.ContactId left join
     ContactBase b
     on o.contactid2 = b.ContactId left join
     ContactBase c
     on o.contactid3 = c.ContactId;

This specifically wants an index on ContractBase(ContactId, ContactName). The use of left join for this purpose is entirely reasonable.

With this index, the original data pages are not even needed by the query, because the index "covers" the query -- all columns in the query are in the index. You don't need to worry about loading the index multiple times. Although the index (or table) is logically loaded three times, something called the page cache will probably keep the pages in memory, so the subsequent loads will be very fast.