The Basics of Oracle Inline View with Examples

View in Oracle are nothing but a name given to SELECT query and is like a virtual table allowing us to execute this query by calling View instead of writing the entire SELECT query again.

Inline Views have a similar function and are used for simplifying complex queries by eliminating join condition in queries or for combining multiple queries into one.

An inline view is more like a subquery that we use after FROM clause in a SELECT statement.

Let’s see a simple SELECT statement:

SELECT
    column1, column2 ...
FROM
    [table/subquery];

In this SELECT clause, either we can specify a table or subquery from which we want to fetch data.

When we use a subquery in place of table, it’s akin to calling a virtual table which has all the data that we want to fetch. Using a Subquery in this SELECT statement makes it a Subselect or an Inline view. This subquery can be also be called a derived table as its fundamentally replacing a ‘table’ after FROM clause.

Syntax:

SELECT
    column_names ...
FROM
    (
        SELECT
            *
        FROM
            table_name
    );

Here,

column_names – It is the list of columns that we want to project.

subquery – SELECT * FROM table_name in this case is a subquery that we used after FROM clause and which we can use to simplify queries by avoiding join operations and condensing multiple queries in one.

Let’s understand this through examples:

Oracle Inline View Example

We will be using Employees table from HR schema for this example:

1. Basic Oracle Inline View Example

We use the following INLINE query for getting a list of 10 employees who are earning the lowest.

SELECT
    *
FROM
    (
        SELECT
            employee_id,
            first_name,
            last_name,
            salary
        FROM
            employees
        ORDER BY
            salary ASC
    )
WHERE
    ROWNUM <= 10;

Result:

In this query, the inline view returned the list of all employees and their salary in ascending order while we use ROWNUM in outer query to limit the number of employees to 10.

2. INLINE VIEW joins with a table Example

We use the following query for joining departments and employees table to get the highest salary in each department.

SELECT
    department_name,
    max_salary
FROM
    departments a,
    (
        SELECT
            department_id,
            MAX( salary) max_salary
        FROM
            employees
        GROUP BY
            department_id
    ) b
WHERE
    a.department_id = b.department_id
ORDER BY
    department_name;

Result:

In this example, the inline view or subquery returned us the list of highest earning employees in each department with their department_id, while we used the outer query for joining the inline view with departments table to get the name of each department.

3. Oracle INLINE VIEW: DML example

In this example, we will see how to use DML,i.e, Data manipulation Language Statements like INSERT, UPDATE and DELETE to update inline view.

The following query is used for increasing the salary of employees in Employee table by 10%.

UPDATE
    (
        SELECT
            salary
        FROM
            employees
        INNER JOIN departments USING (department_id)
        WHERE
            department_name = 'Finance'
    )
SET
    salary = salary * 1.15;

Suppose we want to delete all employees from ‘Marketing’ department having salary less than 2000 then we use this query:

DELETE
    (
        SELECT
            salary
        FROM
            employees
        INNER JOIN departments 
        USING (department_id)
        WHERE
            category_name = 'Marketing'
    )
WHERE
    salary < 2000;

4. LATERAL INLINE VIEW Example

Consider the following statement:

SELECT
    first_name,
    last_name,
    department_name
FROM
    employees e,
    (   SELECT
            *
        FROM
            departments d
        WHERE
            e.department_id = d.departmet_id
    )
ORDER BY
    department_name;

As, we can see Oracle returned an error because the inline view cannot reference tables outside its definition.

ORA-00904: “P”.”CATEGORY_ID”: invalid identifier

This issue was resolved from Oracle 12C onwards where just using the LATERAL keyword besides the inline view can help us reference the table on left side of inline view definition. This can be seen in following query:

SELECT
    first_name,
    last_name,
    department_name
FROM
    employees e,
    LATERAL(   SELECT
            *
        FROM
            departments d
        WHERE
            e.department_id = d.departmet_id
    )
ORDER BY
    department_name;

Note: While using LATERAL keyword, we need to keep some restrictions in mind.

In this article, we learned how to use INLINE views to simplify queries.