Creating Efficient Temporary Views

Some times you need a table or a view of data that does not exist to construct a needed query and do not have the authority to create such a table or view on your database.








At times, within a single SQL statement, you want to create a table “on the fly” that is used solely for your query and will never be used again. In the FROM clause of your query, you normally place the name of your table or view on which to retrieve the data. In cases where a needed view of the data does not exist, you can create a temporary view of that data with what is called an “inline view,” where you specify the characteristics of that view right in the FROM clause of your query:


SELECT last_name, first_name, department_name dept, salary
FROM employees e join
( SELECT department_id, max(salary) high_sal
FROM employees
GROUP BY department_id ) m
USING (department_id) join departments
USING (department_id)
WHERE e.salary = m.high_sal
ORDER BY SALARY desc;