Are subqueries the only option to reuse variables?

Question

I'd like to use MySQL in this form:

SELECT 1 AS one, one*2 AS two

because it's shorter and sweeter than

SELECT one*2 AS two FROM ( SELECT 1 AS one ) AS sub1

but the former doesn't seem to work because it expects one to be a column.

Is there any easier way to accomplish this effect without subqueries?

And no, SELECT 2 AS two is not an option. ;)

Answer 1

select @one := 1 as one, 2 * @one as two;

user-defined variables

Answer 2

Considering this SQL code

SELECT 1 AS one, one*2 AS two

from the perspective of SQL the language (and why not; mysql has a good track record of compliance with the ISO/ANSI SQL Standards), your one is not a variable; rather it is a column correlation name. You cannot use the correlation name in the SELECT clause with the same scope, hence the error.

FWIW your 'shorter and sweeter' syntax does actually work when using the MS Access Database Engine -- is that where you learned it, perchance? Sadly, the Access Database Engine has a poor track record of compliance with the Standards. It is said to take a long time to un-learn Access-speak and learn SQL code ;)