MS SQL Server Interview Question:

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

Submitted by: Administrator

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 ggl_links l LEFT OUTER JOIN ggl_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 ggl_links l LEFT OUTER JOIN ggl_rates r
ON l.id = r.id) s WHERE s.url LIKE '%er%'
GO
<pre>
101 www.globalguideline.com The best
102 www.globalguideline.com/html Well done
103 www.globalguideline.com/xml Thumbs up
107 www.globalguideline.com/sql NULL
</pre>
The error on the first query is caused by the missing alias name to name output of the subquery as a temporary table.
Submitted by: Administrator