Carla Wilson (5/14/2008)
This syntax includes the WITH "CTE" syntax, and on this page, the SELECT statement shows the FROM clause as optional! Perhaps when CTEs were introduced in SQL 2005, this changed the "rules".
The FROM clause is definitely optional since otherwiseSELECT getdate()or (slightly more usefully);with number as (
select 1 as n union all
select 3)
select n from numberwould fail to parse. What isn't clear is how the parser processes field references in a subquery without a FROM clause. As the execution plan shows, it's clearly not obvious and my reaction would be always to include the FROM clause both to make the query clear and so that the behaviour was predictable (and explicable :)).
Searching further, I found the explanation of the FROM clause, SS2K5 or SS2K8, both state
Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).
So the query given, while it parses and runs, is supposed to be invalid syntax, but somehow slips through.
The topic "Subquery Fundamentals" for SS2K, SS2K5 and SS2K8 all state
A subquery nested in the outer SELECT statement has the following components:
A regular SELECT query including the regular select list components.
A regular FROM clause including one or more table or view names.
An optional WHERE clause.
An optional GROUP BY clause.
An optional HAVING clause.
So the requirement to have a FROM clause hasn't changed recently!
Maybe it ought to be logged as a bug with the workaround "don't do this!" 🙂
Derek