Question:
What is the advantage of creating temporary tables?
Answer:
SQL query optimizers, while usually very efficient and accurate, sometimes refuse to evaluate a query in the most efficient manner. In some instances, it improves performance to “guide” the optimizer by breaking an SQL statement into multiple parts and executing them sequentially. This was more of a problem with earlier engines that did not provide the optimizer hints features of latest engines.
Another common use is to hold an intermediate result. Suppose you have a task in which you have to do a query against a large table—such as a customer table, choosing a list of customers, and then, for each customer, performing a series of actions. If the first query were long and time-consuming, it would make sense to run that query once, saving the results in a temp table, and then running the subsequent actions for each row in the temp table, rather than performing the long query many times.
