Tuesday, August 26, 2008

SQL Nested SELECT Statement in FROM Clause

You can create a view by using SELECT statement. For example, a View called as AllEmployeeInfo by SQL statement: SELECT * FROM Employees. Employees is a table. That's quite simple. Then you many list all employee names hired after '2005-01-01' from the view:

SELECT FirstName, LastName
FROM AllEmployeeInfo

Is there any way to use SQL SELECT statement as a subquery directly in FROM clause? For example:

SELECT FirstName, LastName

This does not work in Microsoft SQL 2005. However, if you name the subquery SELECT as an alias, you can do it. Here is the example:

SELECT FirstName, LastName
WHERE HireDate > '2005-01-01') AS Temp
-- or
SELECT FirstName, LastName
FROM (SELECT * FROM Employees) AS Temp
HireDate > '2005-01-01'

It looks like that SQL will create a temporary or dynamic view for the alias, then you could SELECT columns from there. Quite cool? I tried this in PL/SQL Oracle 9.0 but it does not work. Anyway, I like this quick way to get data. Sometimes you may need this kind of nested SELECTs so that you can filter data by several levels and avoid to create unnecessary views.

More on nested SQL queries, read this blog: Using a Subquery in a T-SQL statement.

Notes and Updates on this blog
: Actually, the nested SELECT statement is available in Oracle or PL/SQL as well, but you don't need to AS in the statement. I got this correction from my StackOverFlow Q&A. Thanks for the correction!


Anonymous said...

What would it look like if we were to nest a select statement in with a few LEFT OUTER JOIN's that are not apart of the select statement? I cannot seem to get mine to work.

Is there a rule where the Select Statements need to be right after the FROM and the rest of the parameters follow?