Using a Subquery in the FROM clause : inline view

Inline view:

You can use  a subquery in the FROM clause of a SELECT statement , which is very similar to how views are used. A subquery in the FROM clause of a SELECT statement is also called an inline view.  A subquery in the FROM clause  of a SELECT statement defines a data source for the particular SELECT statement, and only that SELECT statement.  The example below displays employee last names, salaries,department number  and average salaries  for all the employees who earn more than the average salary in their department. The subquery in the FROM clause is named b , the outer query references the SALAVG column using this alias.

SELECT  a.last_name ,a.salary,a.department_id,b.salavg
FROM employees a , ( SELECT department_id,
                                 AVG(salary) salavg
                                 FROM employees 
                                 GROUP BY department_id) b
                                 
WHERE a.department_id =b.department_id
AND a.salary >b.salavg;


LAST_NAME                     SALARY DEPARTMENT_ID     SALAVG
------------------------- ---------- ------------- ----------
Hartstein                      13000            20       9500
Higgins                        12000           110      10150
King                           24000            90 19333.3333
.........
.........
38 rows selected.