• sqlguy-736318 (3/5/2015)


    One benefit of breaking the query into temp tables is that allows the SQL writer to logically group related tables/data.

    For example, course-related tables/data could be grouped into 1 temp table and student-related tables/data could be grouped into a different temp table.

    Flattening the tables means that there are fewer decision points to understand how the data is being joined together.

    Without seeing the exact query you're working on it's hard to say, but I was just pointing out that A) joins are not always the most complex part of a query and B) there is no magical number of too many joins. So given that just saying throw temp tables into the mix because it will automatically make things easier to understand is not true.

    For example if you have someone can't work with a large number of related tables then you might have better luck throwing a view on top of the joins and not even telling them about the actual underlying data structure.