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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×