MySQL equivalent to SQL Server's Cross Apply Values Keyword

Question

    cross apply (
    values
        (col1_id, col1_amt),
        (col2_id, col2_amt),
        (col3_id, col3_amt),
        (col4_id, col4_amt),
        (col5_id, col5_amt),
        (col6_id, col6_amt)
) e (col_id, col_amt)

I want equivalent of this SQL Server syntax in MySQL but can't seem to find any ?

@a_horse_with_no_name Not true as of MySQL 8+, [see here](https://stackoverflow.com/questions/36869221/cross-outer-apply-in-mysql). MySQL now supports lateral joins. — Tim Biegeleisen, Apr 04 '22 at 11:22
i have checked lateral but it seems it does not know values syntax ? — Desmond_, Apr 04 '22 at 11:24

Charlieface · Answer 1 · 2022-04-04T11:53:11.510

3

You can use CROSS JOIN LATERAL in MySQL 8.0.14+. VALUES is also supported from 8.0.19+ (but you need a ROW constructor)

select *
from (values
    row('a')
) v1(x)
cross join lateral (values
    row (concat(v1.x, 'b'))
) v2(y)

db<>fiddle

So for your example, you can do this

cross join lateral
(
    values
    row(fund1_id, fund1_amt),
    row(fund2_id, fund2_amt),
    row(fund3_id, fund3_amt),
    row(fund4_id, fund4_amt),
    row(fund5_id, fund5_amt),
    row(fund6_id, fund6_amt)
)

For an OUTER APPLY you can use LEFT JOIN LATERAL .... ON 1=1

edited Apr 04 '22 at 11:53

answered Apr 04 '22 at 11:24

Charlieface

52,284
6
19
43

can you check if you can use values inside lataral scope – Desmond_ Apr 04 '22 at 11:28
Yes it is, have modified – Charlieface Apr 04 '22 at 11:30
SELECT .. UNION ALL is more simple than VALUES .. ROW(). – Akina Apr 04 '22 at 11:32
@Akina can you share sample ? – Desmond_ Apr 04 '22 at 11:33
```cross join lateral ( values row (concat(v1.x, 'b')) ) v2(y)``` is an equivalent for ```cross join lateral ( SELECT concat(v1.x, 'b') ) v2(y)```. And you may expand this with UNION ALL, for example, ```cross join lateral ( SELECT concat(v1.x, 'b') UNION ALL SELECT concat(v1.x, 'c') ) v2(y)```. – Akina Apr 04 '22 at 11:35
@Desmond_ 1) Insert this link into the question text 2) Add source table - structure and some data. – Akina Apr 04 '22 at 11:38
as this this is sensitive dont want to share it btw – Desmond_ Apr 04 '22 at 11:39
@Desmond_ What exactly is the question? Change `CROSS APPLY` to `CROSS JOIN LATERAL` and add `ROW` in front of each row – Charlieface Apr 04 '22 at 11:40
@Charlieface check the dbfiddle link please i have shared the sql query there – Desmond_ Apr 04 '22 at 11:41
the question is i just want to changes values inside the cross apply with mysql syntax should i replace values with ROW ? – Desmond_ Apr 04 '22 at 11:45
1

OK have modified further – Charlieface Apr 04 '22 at 11:53

Luuk · Answer 2 · 2022-04-04T12:42:10.873

0

You need to add ROW before every row:

cross apply (
    values
        ROW(col1_id, col1_amt),
        ROW(col2_id, col2_amt),
        ROW(col3_id, col3_amt),
        ROW(col4_id, col4_amt),
        ROW(col5_id, col5_amt),
        ROW(col6_id, col6_amt)
) e (col_id, col_amt)

EDIT: A simple example to show how VALUES and ROW should work is this DBFIDDLE

edited Apr 04 '22 at 12:42

answered Apr 04 '22 at 11:45

Luuk

12,245
5
22
33

it does not work – Desmond_ Apr 04 '22 at 11:47
@Desmond_ "does not work" is not an useful comment. People are trying to help YOU - make an effort to provide useful information. – SMor Apr 04 '22 at 12:27
I added a (simple!) example of how VALUES and ROW should work in MySQL – Luuk Apr 04 '22 at 12:42

MySQL equivalent to SQL Server's Cross Apply Values Keyword

2 Answers2