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.

⇒ Counting Groups Returned with the GROUP BY Clause in SQL Server

⇐ Using Subqueries with the EXISTS Operators in SQL Server

⇑ Using SELECT Statements with Joins and Subqueries in SQL Server

⇑⇑ SQL Server Database Tutorials