Early (or re-ordered) re-use of derived columns in a query - is this valid ANSI SQL?

Question

Is this valid ANSI SQL?:

SELECT 1 AS X
       ,2 * X AS Y
       ,3 * Y AS Z

Because Teradata (12) can do this, as well as this (yes, crazy isn't it):

SELECT 3 * Y AS Z
       ,2 * X AS Y
       ,1 AS X

But SQL Server 2005 requires something like this:

SELECT  X
       ,Y
       ,3 * Y AS Z
FROM    (
         SELECT X
               ,2 * X AS Y
         FROM   (
                 SELECT 1 AS X
                ) AS X
        ) AS Y

Ovarian

I wonder what error it throws if you try SELECT 2*y AS x, 2*x AS y – Bina

Answer 1

No, it's not valid ANSI. ANSI assumes that all SELECT clause items are evaluated at once.

And I'd've written it in SQL 2005 as:

SELECT *
FROM        (SELECT 1 AS X) X
CROSS APPLY (SELECT 2 * X AS Y) Y
CROSS APPLY (SELECT 3 * Y AS Z) Z
;

Bulldoze

A clever alternative to the CTEs - I'll keep that in mind. Unfortunately, Teradata supports neither stacked CTEs (I believe it might support one, so that's on my list to investigate) nor the APPLY operator. – Ovarian

Of course re-using aliases in Teradata is not ANSI SQL, but CROSS APPLY is also an extension to Standard SQL :-) – Karmen

Answer 2

It doesn't need to be that ugly in SQL Server 2005+. That's why Microsoft introduced CTEs:

WITH T1 AS (SELECT 1 AS X),
     T2 AS (SELECT X, 2 * X AS Y FROM T1)
SELECT X, Y, 3 * Y AS Z FROM T2

Or you could use CROSS APPLY as Rob demonstrates - that may or may not work for you depending on the specifics of the query.

I admit that it's not as clean as Teradata's, but it's not nearly as bad as the subquery version, and the original Teradata example in your question is definitely not part of the SQL-92 standard.

I'd also add that in your original example, the X, Y and Z columns are not, technically, derived columns as you call them. At least as far as Microsoft and ANSI are concerned, they are just aliases, and an alias can't refer to another alias until it actually becomes a column (i.e. through a subquery or CTE).

Langmuir

We typically use multiple stacked CTEs in SQL Server. Unfortunately, they aren't allowed in Teradata ;-( I'm trying to find a middle ground. What I am finding is that Teradata performs A LOT better when you don't nest, and use this trick, but it worries me that it might not continue to work and I would have been better off leaving my CTEs converted to nesting if this breaks in Teradata in the future. – Ovarian