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.
- The syntax for an inline view is,
Read AlsoReplace SQL.
Example 1: Inline View:
- Display the top five earner names and salaries from the EMPLOYEES table:
- 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:
- 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:
Read AlsoInsert into select.
- 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.