derived table doesn't exist in FROM clause of

问题:

I was reading a T-SQL book which says

... derived tables are defined in the FROM clause of the outer query and not prior to the outer query. As far as the FROM clause of the outer query is concerned, the derived table doesn’t exist yet; therefore, if you need to refer to multiple instances of the derived table, you can’t.

which means we can do this as a cross join:

SELECT *
FROM (
    SELECT empid FROM Sales.Orders
) AS D, D

the query above produces invalid object D error.

It sounds fair enough, but if derived table doesn’t exist yet, how come below query is valid:

SELECT *
FROM (
    SELECT empid FROM Sales.Orders
) AS D
WHERE D.empid = 12

I mean if D doesn't exist yet, how can we access the column by D.empid?

回答1:

The order in which SQL is evaluated is*:

1. FROM (tables, derived tables and joined tables)
2. WHERE
3. SELECT

So D is not available inside the FROM clause but it is available in the WHERE clause.

---

* Only the operations used in your question are shown. The complete list is available here.