Inline View in SQL - sql - sql tutorial - learn sql



What is inline view in SQL ?

  • An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used to simplify complex queries by removing join operations and condensing several separate queries into a single query.
    • As mentioned in the View section, a view is a virtual table that has the characteristics of a table yet does not hold any actual data.
    • In an inline view construct, instead of specifying table name(s) after the FROM keyword, the source of the data actually comes from the inline view.
    • Inline view is sometimes referred to as derived table. These two terms are used interchangeably.
    • It is a subquery that appears in the From clause of the Select statement.
    • The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
    • This subquery is enclosed in parenthesis and may be given an alias name. The columns selected in the subquery can be referenced in the parent query.

Syntax:

  • The syntax for an inline view is,
SELECT "column_name" FROM (Inline View);

Read Also

Replace SQL.

Example 1: 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:

  • Assume we have two tables: The first table is User_Address, which maps each user to a ZIP code; the second table is User_Score, which records all the scores of each user.

The question is, How to write a SQL query to find the number of users who scored higher than 200 for each ZIP code ?
Without using an inline view, we can accomplish this in two steps:

Query 1

CREATE TABLE User_Higher_Than_200 
SELECT User_ID, SUM(Score) FROM User_Score 
GROUP BY User_ID 
HAVING SUM(Score) > 200

Query 2

SELECT a2.ZIP_CODE, COUNT(a1.User_ID) 
FROM User_Higher_Than_200 a1, User_Address a2 
WHERE a1.User_ID = a2.ZIP_CODE 
GROUP BY a2.ZIP_CODE;
  • In the above code, we introduced a temporary table, User_Higher_Than_200, to store the list of users who scored higher than 200. User_Higher_Than_200 is then used to join to the User_Address table to get the final result.
  • We can simplify the above SQL using the inline view construct as follows:

Query 3

SELECT a2.ZIP_CODE, COUNT(a1.User_ID) 
FROM 
(SELECT User_ID, SUM(Score) FROM User_Score GROUP BY User_ID HAVING SUM(Score) > 200) a1, 
User_Address a2 
WHERE a1.User_ID = a2.ZIP_CODE 
GROUP BY a2.ZIP_CODE;
  • There are two advantages on using inline view :
    • We do not need to create the temporary table. This prevents the database from having too many objects, which is a good thing as each additional object in the database costs resources to manage.
    • We can use a single SQL query to accomplish what we want.

Related Searches to Inline View in SQL - SQL tutorial