How To Use Subqueries in the FROM Clause in SQL Server?

If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. A subquery used in this way become a temporary table, and you must provide a table alias name for the subquery as in "SELECT ... FROM (SELECT ...) aliasName". The following statement shows you how to use a subquery as base table for the main query:

SELECT * FROM (SELECT l.id, l.url, r.comment
   FROM fyi_links l LEFT OUTER JOIN fyi_rates r 
   ON l.id = r.id) WHERE url LIKE '%er%'
GO
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.

SELECT * FROM (SELECT l.id, l.url, r.comment
   FROM fyi_links l LEFT OUTER JOIN fyi_rates r 
   ON l.id = r.id) s WHERE s.url LIKE '%er%'
GO
101	dev.fyicenter.com	The best
102	dba.fyicenter.com	Well done
103	sqa.fyicenter.com	Thumbs up
107	www.winrunner.com	NULL

The error on the first query is caused by the missing alias name to name output of the subquery as a temporary table.

⇒Using SELECT Statements with Joins and Subqueries in SQL Server

⇒⇒SQL Server Database Tutorials