This is a pattern in SQL queries that I've found myself repeating recently:SELECT    w,    x,    y,    (w + x) / y as zFROM  (SELECT      <some gigantic and complicated query> 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 namedirectly reusing the new column doesn't work in MySQL (unsure about other RDBMS's)This code fails:SELECT    <some gigantic and complicated query> as w,    a + b as x,    a - b as y,    (w + x) / y as zFROM    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 subquerythis renames the columnsuse the new column names from the outer queryI'm unsure whether it's a best practice, or a common practice, and thus easily understandable for other programmers.
Using a subquery to rename columns
mysql;sql
I think your practice is fine. Here are the two queries and their explain results:explain extended SELECT    w,    x,    y,    (w + x) / y as zFROM  (SELECT       (SELECT SUM(s) FROM test2) as w,      a + b as x,      a - b as y   FROM      test) somealias;+----+-------------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |+----+-------------+------------+------+---------------+------+---------+------+------+----------+-------+|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |       ||  2 | DERIVED     | test       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |       ||  3 | SUBQUERY    | test2      | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |       |+----+-------------+------------+------+---------------+------+---------+------+------+----------+-------+explain extended SELECT       (SELECT SUM(s) FROM test2) as w,      a + b as x,      a - b as y,      ((SELECT SUM(s) FROM test2) + (a + b)) / (a -b) as z   FROM      test+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+|  1 | PRIMARY     | test  | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |       ||  3 | SUBQUERY    | test2 | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |       ||  2 | SUBQUERY    | test2 | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |       |+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+If I'm right the second version runs the complex (maybe bottleneck) subquery on table test2 twice, so it should be slower than the first query which runs the complex subquery only once. Furthermore, the first one is much easier to read. So, I prefer the first one. (I'm not a MySQL guru, feel free to correct me.)