Can a query be used in place of a table in SQL Server

Question

This should really be allowed - I do not understand why it is not.

SELECT * 
FROM (
    SELECT * 
    FROM MyTable
)

Answer 1

There are at least two ways to accomplish this, but what you might be looking for is a Common Table Expression (CTE), introduced in SQL Server 2005.

From the above link:

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

Alternately, you can create a View, which is a permanent table-shaped representation of a query that you can access by name:

USE AdventureWorks ;
GO
IF OBJECT_ID ('hiredate_view', 'V') IS NOT NULL
DROP VIEW hiredate_view ;
GO
CREATE VIEW hiredate_view
AS 
SELECT c.FirstName, c.LastName, e.EmployeeID, e.HireDate
FROM HumanResources.Employee e JOIN Person.Contact c on e.ContactID = c.ContactID ;
GO
SELECT * FROM hiredate_view

Answer 2

If you add a table alias it should work:

SELECT * 
FROM (
    SELECT * 
    FROM MyTable
) as A

Answer 3

You are missing an 'alias' on the sub-query (I added an alias 'X' )

SELECT * 
FROM (
    SELECT * 
    FROM MyTable
) X

Answer 4

In SQL Server it is allowed, but the inner select has to be given a name, such as:

SELECT *  
FROM ( 
    SELECT *  
    FROM MyTable
) m

When a name is not supplied it will throw an incorrect syntax error near ')' message.