RSS Feed

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;