SQL Inline View Subquery

SQL Subqueries

SQL Correlated subquery

SQL Scalar subquery

When you use SQL Subquery in From clause of the select statement it is called inline view.

A common use for inline views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query. A subquery which is enclosed in parenthesis in the FROM clause may be given an alias name. The columns selected in the subquery can be referenced in the parent query, just as you would select from any normal table or view.

Example 1 of Inline View

Display the top five earner names and salaries from the EMPLOYEES table:

SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name, salary
      FROM employees
      ORDER BY salary DESC)
WHERE ROWNUM <= 5; 

Example 2 of Inline View

Calculate the number of employees in each department

SELECT d.dept_id, d.name, emp_cnt.tot
FROM department d, (SELECT dept_id, count(*) tot
                    FROM employee
                    GROUP BY dept_id) emp_cnt
WHERE d.dept_id = emp_cnt.dept_id;