tags:

views:

778

answers:

6

I recently answered this question how-to-call-user-defined-function-in-order-to-use-with-select-group-by-order-by

My answer was to use an inline view to perform the function and then group on that.

In comments the asker has not understood my response and has asked for some sites / references to help explain it.

I've done a quick google and haven't found any great resources that explain in detail what an inline view is and where they are useful.

Does anyone have anything that can help to explain what an inline view is?

+4  A: 

From here: An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query.

Otávio Décio
for the examples in the given link, I call those "derived tables", do a google on than and you will find similar examples and articles
KM
@KM - I wouldn't doubt it, people feel comfortable with different names for concepts depending on their background.
Otávio Décio
search sql server help for "derived tables", they are mentioned in the first line of the documentation for the "FROM (Transact-SQL)" entry. I didn't see anything on "inline views".
KM
+2  A: 
Tomalak
That being said, I am no fan of the cargo cult that is behind using the "correct" terminology. Personally I never used "inline view" or "derived table" or whatnot. It's a sub-query -- there is no need for this fine-grained distinction. (Well, maybe it's just me.)
Tomalak
+2  A: 

I think another term (possibly a SQL Server term) is 'derived table'

For instance, this article:

http://www.mssqltips.com/tip.asp?tip=1042

or

http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values

JDU
A: 

this query has an inline view:

Get all persons emails to send a newsletter that their organization was present in all events occurred February 2009:

SELECT p.firstname, p.lastname, p.emailaddress
FROM Persons p
WHERE organizationnr IN (
   SELECT organization_id 
   FROM Events 
   WHERE MONTH(EventDate) = 2 AND YEAR(EventDate) = 2009
)
ORDER BY p.lastname, p.firstname;

the sub query

SELECT organization_id 
FROM Events 
WHERE MONTH(EventDate) = 2 AND YEAR(EventDate) = 2009

is the Inline View, because you can extract this and create a SQL View, and use that view name instead the entire code.

balexandre
A: 

This MSDN article claims that an inline view is another term for a derived table, and refers to the same thing everyone else is describing in this thread (subquery in FROM). An alternative in sql server 2005 and up is the common table expression.

A: 

Another term often more commonly used for an inline view is 'Embedded Select Statement'

So a select inside a select.

northpole