MS SQL Server Concepts and Programming Question:
Download Questions PDF

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

Answer:

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.

Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
How To Use Subqueries with the EXISTS Operators in MS SQL Server?How To Count Groups Returned with the GROUP BY Clause in MS SQL Server?