Using a subquery to rename columns

This is a pattern in SQL queries that I've found myself repeating recently:

SELECT
    w,
    x,
    y,
    (w + x) / y as z
FROM
  (SELECT
       as w,
      a + b as x,
      a - b as y
   FROM
      basetable) somealias;

The issue is:

• a select query that does some complex operations, resulting in a column with a new name

• directly reusing the "new" column doesn't work in MySQL (unsure about other RDBMS's)

This code fails:

SELECT
     as w,
    a + b as x,
    a - b as y,
    (w + x) / y as z
FROM
    basetable;

Because MySQL doesn't allow column aliases (x and y) to be re-used in the same select query.

The solution I'm using:

• wrap the select in a subquery

• this renames the columns

• use the new column names from the outer query

I'm unsure whether it's a best practice, or a common practice, and thus easily understandable for other programmers.

As long as the variables are well-named, I think your way (subquery) will be clear and therefore quite acceptable, though I'm not quite sure whether it will affect performance or to what extent (try benchmarking).

That said, I'd still urge you to use a prepared query or stored procedure or function, I think that would make it even more clear, and potentially faster.

StackExchange Code Review Q#6221, answer score: 3