Re: performance advice needed: join vs explicit subselect

Karsten Hilbert wrote:
> Hello all,
>
> maybe some general advice can be had on this:
>
> table test_results
>     modified_by integer foreign key staff(pk),
>     intended_reviewer integer foreign key staff(pk),
>     actual_reviewer integer foreign key staff(pk)
>
> (this table will contain millions of rows)
>
> table staff
>     pk integer
>     name text
>
> (this table will contain at most 50 rows)
>
> Now I want to set up a view which aggregates test results
> with staff names for all three foreign keys. This would mean
> I would either have to
>
> - join test_results to staff three times, once for each
>   of the foreign keys, this is going to be messy with
>   tracking table aliases, duplicate column names etc
>
> - write three explicit sub-selects for the columns I want
>   to denormalize into the view definition
>
>
Select testresults.*, Modifer.Name, Intended.name,  Actual.name   from
testresults
    left join (Select pk, name  from staff) Modifer
         on Modifer.pk  = testresults.modified_by
    left join (Select pk, name  from staff) Intended
        on Reviewer.pk  = testresults.intended_reviewer
    left join (Select pk, name  from staff) Actual
        on pk  = testresults.actual_reviewer


This is what i think you are after.  You can do this via nested queries
also for each name