|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
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
GROUP BY dept_id) emp_cnt
WHERE d.dept_id = emp_cnt.dept_id;