Inline View FAQS

1. What Is an Inline View?

An Inline View is a subquery in the FROM clause that:

• Acts like a temporary table or view
• Provides a derived table for the outer query
• Exists only for the duration of the query execution

Syntax:

SELECT outer_columns

FROM (

    SELECT inner_columns

    FROM table_name

    WHERE conditions

) alias_name

WHERE outer_conditions;

2. Why Use an Inline View?

• Simplifies complex queries
• Allows aggregation before outer filtering
• Enables joining summarized results
• Avoids creating permanent views or temporary tables

3. Can Inline Views Have Aggregation?

Yes. Common use is aggregating data before filtering.

Example:

SELECT department_id, avg_sal

FROM (

    SELECT department_id, AVG(salary) AS avg_sal

    FROM employees

    GROUP BY department_id

) dept_avg

WHERE avg_sal > 5000;

Inner query calculates department averages.
Outer query filters departments with avg salary > 5000.

4. Can Inline Views Be Joined?

Yes. Inline views can be treated like regular tables in joins.

Example:

SELECT e.employee_id, d.avg_sal

FROM employees e

JOIN (

    SELECT department_id, AVG(salary) AS avg_sal

    FROM employees

    GROUP BY department_id

) d

ON e.department_id = d.department_id

WHERE e.salary > d.avg_sal;

Joins employees with department averages and filters accordingly.

5. Difference Between Inline View and Subquery in WHERE

Feature	Inline View	WHERE Subquery
Location	FROM clause	WHERE/HAVING/SELECT
Acts Like	Temporary table	Single value or list
Can use	Aggregates, multiple columns	Single or multiple values
Can join	Yes	Usually not

6. Can Inline View Be Correlated?

❌ No.

• Inline views cannot reference outer query columns.
• If inner query references outer columns → becomes a correlated subquery.

7. Can Inline View Return Multiple Columns?

✅ Yes.

Example:

SELECT e.employee_id, d.department_id, d.avg_sal

FROM employees e

JOIN (

    SELECT department_id, AVG(salary) AS avg_sal, COUNT(*) AS emp_count

    FROM employees

    GROUP BY department_id

) d

ON e.department_id = d.department_id;

8. Inline View in UPDATE Statement

Example:

UPDATE employees e

SET e.salary = (

    SELECT d.avg_sal

    FROM (

        SELECT department_id, AVG(salary) AS avg_sal

        FROM employees

        GROUP BY department_id

    ) d

    WHERE e.department_id = d.department_id

)

WHERE e.department_id = 10;

Inner inline view calculates department averages; outer query updates salaries.

9. Inline View in DELETE Statement

Example:

DELETE FROM employees

WHERE employee_id IN (

    SELECT employee_id

    FROM (

        SELECT employee_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) rnk

        FROM employees

    ) temp

    WHERE rnk > 5

);

Deletes employees not in top 5 salaries per department.

10. Inline View vs WITH Clause (CTE)

Feature	Inline View	WITH Clause (CTE)
Scope	Only in FROM clause	Named temporary result, can be reused
Readability	Nested	Often cleaner
Reuse	No	Yes
Performance	Optimizer may merge	Optimizer may merge

11. Performance Considerations

• Optimized by Oracle; may merge with outer query
• Indexes on underlying tables still apply
• For large datasets:
• Avoid excessive nested inline views
• Use analytic functions if possible
• Check execution plan

12. Real-World Business Example

Requirement:

Find employees earning more than department average.

SELECT e.employee_id, e.salary, d.avg_sal

FROM employees e

JOIN (

    SELECT department_id, AVG(salary) AS avg_sal

    FROM employees

    GROUP BY department_id

) d

ON e.department_id = d.department_id

WHERE e.salary > d.avg_sal;

13. Common Mistakes

• Forgetting alias for inline view → ORA errors
• Using correlated columns inside inline view
• Over-nesting inline views
• Assuming inline views always improve performance

14. Best Practices

• Always provide an alias
• Use inline views for aggregation before filtering
• Prefer analytic functions for per-row calculations
• Check execution plan for complex views
• Simplify nested inline views when possible

15. Short Interview Answer

“An inline view is a subquery in the FROM clause that acts as a temporary table. It allows aggregation, derived columns, and joins within a single query. It must have an alias and exists only for the duration of the query.”