SQL Inline View

An inline view is a SELECT statement in the FROM clause. 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.

An inline view is sometimes referred to as a derived table. These two terms are used interchangeably.

Syntax

The syntax for an inline view is,

SELECT "column_name" FROM (Inline View);

Example

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.User_ID
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.User_ID
GROUP BY a2.ZIP_CODE;

The code in the inline view represents the subquery in the FROM clause. There are two advantages on using inline view here:

1. 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.

2. We can use a single SQL query to accomplish what we want.

Notice that we treat the inline view exactly the same as we treat a table. Comparing Query 2 and Query 3, we see that the only difference is that we replace the temporary table name in Query 2 with the inline view statement in Query 3. Everything else stays the same.