MySQL equivalent to MS SQL's Cross Apply

Question

If you're used to MS SQL's Cross Apply, then you may wonder how to accomplish the same thing in MySQL. I found the reverse question and thought the direct question may help anyone who'll ever search how to migrate this functionality from MS SQL to MySQL.

In this example, Cross Apply lets you modify a field and use the result within the very same query. The question is how to do it in MySQL.

SELECT v.Var1, POWER(v.Var1, 2) AS Var2Squared
    FROM [Table] t
    CROSS APPLY (SELECT t.Column1 + t.Column2 AS Var1) v

Etienne

I find CROSS APPLY particularly useful for joining on another table based on the maximum or minimum value of a field therein. At the moment I'm scratching my head (metaphorically speaking) for the best way to solve this problem in MySQL. – Saintebeuve

Then please consider voting for this question. – Etienne

Answer 1

Your answer to your own question suggests a very limited subset of what CROSS APPLY does. For this particular problem, you can use a subquery:

select t.*, power(var1, 2)
from (select (column1 + column2) as var1
      from aTable
     ) t;

This is more expensive, because it incurs the expense of materializing the subquery. The alternative is to repeat the expression:

select (column1 + column2) as var1, power((column1 + column2), 2)
from aTable;

These are the only safe ways that I can readily think of in MySQL to do what you want.

Pedersen

Like you said yourself, it's more expensive. Also, this "power(var1, 2)" is just an example. Imagine implementing a repetition with a much more complicated formula which includes cases and whatnot. – Etienne

@Etienne . . . If you don't want the intermediate table, then use a view. I would not recommend using "features" that the documentation explicitly warns against using. – Pedersen

But if the external Select is a view too, it means having 2 views for everything that needs this, since sub-queries aren't allowed in Views. BTW, why do you think a View is better than a sub-query? – Etienne

@Etienne . . . MySQL materializes a subquery but not a view. – Pedersen

The need for 2 views aside, why is it better not to have a sub-query? Doesn't materialization serve a purpose? – Etienne

@Etienne . . . No, it really doesn't. It just adds overhead to the query. There are circumstances where it is useful (so it is an option the optimizer should be able to use under the right circumstances), but other major databases (including free ones such as Postgres and Hive) do not automatically materialize subqueries in the FROM clause. – Pedersen

Answer 2

The same thing can (update: although not ideally) be accomplished in MySQL via User-Defined Variables ("at sign / @"):

SELECT @var1 := column1+column2 AS var1,
  POWER(@var1, 2) AS var2squared
  FROM aTable

Etienne

Just wondering. Can it be later used in WHERE or GROUP BY conditions? – Seaman

This is incorrect. MySQL documentation is quite clear that the order of evaluation of a SELECT is not determined, so this query could have unexpected behavior. – Pedersen

@Etienne . . . Just because it works in one query does not mean it is correct or safe. The documentation is quite explicit: "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. " (dev.mysql.com/doc/refman/5.6/en/user-variables.html). – Pedersen

The power of the APPLY operator (which, BTW, has been part of the ANSI SQL standard for some time now, so shame on MySql for not having it) is when you want more than one column from the applied subquery; it allows the DB to only need to run one subquery for all of the columns you want to bring back. Additionally, you can use these values for things like joins, GROUP BY and windowing functions (which are also part of the ANSI standard and which MySql also still does not support). Really, if you want an open source DB MySql has been out-classed in most every category by Postgresql for years – Jetblack

One thing MySQL has going for it is that it's what's available in Google Cloud Platform. ;-) – Etienne