mySQL: compared values of the same column based on category of another column (no JOIN)

I want to take the difference of the values of 1 column based on version column (only 2 versions) of another column. the id-value pair may exist in one version but not the other and vice versa (in this case, the difference defaults to 0). It’s essentially converting tables from long to wide format with the addition of calculating the difference of the value column.

I know I can use JOIN to achieve this result. But i am wondering if there are methods without JOIN

version_1 and version_2 is defined by users. and in current example, 2021 is version_1 and 2022 is version_2.

table:

 id    version    value    
 1     2021       200      
 2     2021       300      
 4     2021       100      
 1     2022       400      
 2     2022       400      
 3     2022       500      

desired result:

id    version_1   value_v1   version_2    value_v2  difference
 1     2021       200         2022          400     200
 2     2021       300         2022          400     100   
 3     NULL       NULL        2022         500      500
 4     2021       100         NULL         NULL     -100  

Answer

Hmmm . . . you seem to want conditional aggregation:

select id,
       max(case when version = 2021 then value end) as version_1,
       coalesce(max(case when version = 2021 then value end),
                max(case when version = 2022 then value end)
               ) as value,
       max(case when version = 2022 then value end) as version_2,
       (case when min(version) = max(version) then 0
             else max(case when version = 2022 then value end) - max(case when version = 2021 then value end)
        end) as difference
from t
group by id;