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
  FROM SELECT * FROM Employees

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
  FROM (SELECT * FROM Employees
          WHERE HireDate > '2005-01-01') AS Temp
-- or
SELECT FirstName, LastName
  FROM (SELECT * FROM Employees) AS Temp
  WHERE 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!