Here is a tip for those learning to write inline views in SQL. When you write SQL it doesn’t hurt to be polite. Ok, now I’ve lost you. Let me explain.
An inline view is when you write a SQL Statement and then use it as input in another SQL Statement. For this reason it is called an inline view. It works like a view, which is nothing more than an SQL Statement, the query result of which may be used as input to another query. The reason it is called an inline view is that there is no physical view object created. The inline view only lives within the scope of the query in which it is called. Let me give some code to explain the concept.
First, let’s start with a query that returns a filtered set of Contact data.
SELECT c.ContactId, c.FirstName, c.LastName, c.State
FROM Contacts c
WHERE c.State = ‘CA’
This is a simple query returning some data from a Contact table, filtered to return only those contact who’s State = ‘CA’. In the USA this might be those individuals living in the state of California, for which the abbreviation is CA. You don’t know, and I guess that really isn’t relative, except that we only return Contacts with State = ‘CA’.
You could create a View, say CaliforniaContacts, using this query, and the view could be used in any other query, or even stored procedures or functions. However, there are times when you need a set such as this with a filter, but it is not something worth persisting, because it won’t be used anywhere else. That’s when we create an inline view as in the following example.
SELECT *
FROM
(
SELECT c.ContactId, c.FirstName, c.LastName, c.State
FROM Contacts c
WHERE c.State = ‘CA’
)
If you tried writing a similar query to the one above, SQL Server returned an error. It doesn’t understand the contents within the parenthesis. In order to identify the query as an inline view you have to provide an alias. That goes back to my first statement about being polite. The following query works perfectly.
SELECT *
FROM
(
SELECT c.ContactId, c.FirstName, c.LastName, c.State
FROM Contacts c
WHERE c.State = ‘CA’
) AS Please
It really doesn’t matter what name the alias. You could have used CaliforniaContacts in place of Please, and it would work just the same. The point is, you have to provide an alias. Writing Polite SQL was a gimmick I used to remember the syntax years ago.
There is an additional value of assigning an alias. Once you assign an alias to the inline view, you can join it to other tables, views, functions, or inline views. Personally, many times I prefer to use Common Table Expressions (CTVs) in place of inline views, because you declare them before you consume them. When you create the inline view, you statement consumes the view contents before it is defined in the code. When inline view(s) become complex, it can be difficult to follow what is going on in the entire query.
So, remember when using inline views to always be polite.
Cheers,
Ben