3

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
Community
  • 1
  • 1
LWC
  • 1,084
  • 1
  • 10
  • 28
  • 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. – Stewart Oct 27 '19 at 13:12
  • Then please consider voting for this question. – LWC Oct 28 '19 at 06:05

2 Answers2

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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. – LWC Oct 01 '15 at 18:24
  • @LWC . . . 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. – Gordon Linoff Oct 02 '15 at 01:50
  • 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? – LWC Oct 02 '15 at 18:09
  • @LWC . . . MySQL materializes a subquery but not a view. – Gordon Linoff Oct 03 '15 at 02:27
  • The need for 2 views aside, why is it better not to have a sub-query? Doesn't materialization serve a purpose? – LWC Oct 03 '15 at 13:45
  • 1
    @LWC . . . 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. – Gordon Linoff Oct 03 '15 at 22:36
0

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
LWC
  • 1,084
  • 1
  • 10
  • 28
  • Just wondering. Can it be later used in `WHERE` or `GROUP BY` conditions? – Evaldas Buinauskas Oct 01 '15 at 18:09
  • 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. – Gordon Linoff Oct 01 '15 at 18:14
  • @LWC . . . 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. " (https://dev.mysql.com/doc/refman/5.6/en/user-variables.html). – Gordon Linoff Oct 01 '15 at 18:18
  • 2
    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 – Joel Coehoorn Oct 01 '15 at 18:23
  • One thing MySQL has going for it is that it's what's available in Google Cloud Platform. ;-) – LWC Oct 01 '15 at 18:49