Photo by spaceamoeba

SQL is a deceptively simple language, there are very few keywords and it doesn’t take long to become proficient at simple SELECT, UPDATE and DELETE statements. However those few simple keywords can be built upon for ever more increasingly complex statements. This is the first in a series of articles on SQL, intended to aid others with understanding those basic building blocks and recombining them to achieve new heights with SQL.

This article will be a quick primer for inline views. Inline views is a concept that once learned, can be used to create many complex queries, that previously took multiple statements and temporary tables to accomplish.

Note: At the end of the article you will find download links to scripts (for PostgreSQL and MS-SQL) for the sample tables, data, and statements used in this article. The examples show are in PostgreSQL syntax.

So what exactly is an inline view? An inline view is a SELECT statement in the FROM clause of another SELECT statement. I bet that really cleared it up, didn’t it!

Let’s start with what a view is. In SQL you can create a view, which is a select statement that can then be treated as a table for later select statements. An inline view is simply a dynamic view created as part of a SELECT statement. This means that the columns selected in the view can be referenced in other parts of the SELECT statement.

Perhaps a simple example will help:

select customername,totals.sales,totals.shipping from customer
inner join (select cstsysid as cstsysid,sum(totalsale) as sales,
sum(shipping) as shipping from sales group by cstsysid) as totals
on customer.cstsysid = totals.cstsysid

In the above example the sub-query aliased as totals is our inline view.  Notice how we performed a join between our customer table and our inline view, and selected fields from it. This behaves exactly the same as if we had declared a view and used it in our select statement like the following:

create view totals as select cstsysid as cstsysid,
sum(totalsale) as sales,sum(shipping) as shipping
from sales group by cstsysid;

select customername,totals.sales,totals.shipping
from customer inner join totals
on customer.cstsysid = totals.cstsysid;

So why would we want to create an inline view instead of creating an actual view? Views are great tools for summarized data that we will use often, but what happens when the Boss comes in and wants to know some arcane aggregate that you haven’t created a view for and he needs it now! With an inline view you are able to write one select statement that does the aggregate functions and joins it with other data. The Boss is happy and so are you!

So what else can we do with inline views? Remember that crazy Boss that is always busting in the door needing random data, what if he wants to see the average daily commission for all salesmen that had an average over $20 per day, sorted from highest to lowest. In our example data the formula for calculating average daily commission is “(totalsales*commissionrate)/daysworked”. I know not the best designed table but good enough for our example. Well back in the dark days before I learned about inline views I might have written a statement similar to this:

select salesmanname,
cast((totalsales*commissionrate)/daysworked as numeric(10,2)) as avgdailycommission
from salesman
where cast((totalsales*commissionrate)/daysworked as numeric(10,2)) > 20
order by avgdailycommission desc

You may be saying that’s not so bad, but the statement is already getting a little unwieldly, and what if the calculation was even more complex? Worst, what happens when we (gasp) realized that we made a mistake in the formula, now we have to change it in two places! And if we mess up and only change it in one, then we really have a nightmare brewing and the Boss isn’t going to be happy with us!

I bet someone out there in blog land is saying, “But wait a minute, you are using avgdailycommission in the ORDER BY clause why not use it in the WHERE clause? To answer that question we have to did deep into the SQL documentation where we will find that the WHERE clause is evaluated before the SELECT clause, which means that when the WHERE clause is evaluated the alias avgdailycommission doesn’t exists yet. On the other hand the ORDER BY clause is evaluated after the SELECT so when it is evaluated the alias does exists.  So what’s an aspiring SQL rockstar to do? Pull that nifty inline view trick out of your bag and write the statement like this:

select * from
(select salesmanname,
cast((totalsales*commissionrate)/daysworked as numeric(10,2)) as avgdailycommission
from salesman) as s1
where avgdailycommission > 20
order by avgdailycommission desc

We took our original SELECT statement and treated it as an inline view by simply wrapping it with parenthesis and including it in an outer SELECT statement. Now we can reference our alias in the WHERE clause of the outer statement. So what’s with aliasing the SELECT statement as “s1” you may ask? Well some, but not all, databases require the inline view to be aliased. All of them accept an alias, so its best to get in the habit of always including the alias to make the code less database dependent.

Summary

This was just a brief primer for inline views, they have many other uses, and as with all SQL methods it’s a great building block that can be combined with other techniques to accomplish incredibly complex things in a single SQL statement. I hope this article inspires you to begin using inline views more in your daily work. And if an inline view has help save your bacon at work, please share in the comments. I look forward to creating more articles in the SQL 101 series, and if there is a topic you would like to suggest please feel free to send your suggestions.

[suffusion-the-author display=’author’]

[suffusion-the-author display=’description’]

To download a script that has the tables, data, and scripts used in this article please select the proper link below:

PostgreSQL Script

MS SQL Server Script

• PostgreSQL