Is there a correct way to utilise SQL UNIONs in order to select the max value from three potential queries?

Question

I'm writing a Stored Procedure in SQL and as part of it, I need to select the maximum of three potential values (demand, average and forecast). I've got the queries to find each of these values, but I'm wondering which of the following options would be the best practice:

Option 1: Select all three queries into temporary tables, then use a CASE WHEN statement to select the biggest value of the three for each row.

Option 2: Use UNIONS to combine my three queries into one overarching select statement.

I don't even know if Option 2 is possible, so if it isn't then I will just use temporary tables, but I can't imagine that this is the most efficient way of writing what I want. Because I'm using this in a stored procedure, it's difficult for me to select a test material to try the code on so I would appreciate any advice on best writing practice for this, and if it doesn't work when I test the procedure I can raise another question on here with more specific code.

Option 1: (I know this code would work, but it seems too convoluted to be the most efficient option)

SELECT Plant, Material, Dmd
INTO #T1
FROM Table1

SELECT Plant, Material, Avg
FROM #T2
FROM Table2

SELECT Plant, Material, Fcst
INTO #T3
FROM Table3

SELECT a.Plant, 
       a.Material, 
       CASE WHEN Dmd > Avg AND Dmd > Fcst THEN Dmd ELSE
       CASE WHEN Avg > Fcst THEN Avg ELSE Fcst END END as 'Output'
FROM #T1 a INNER JOIN
     #T2 b on a.Plant = b.Plant AND a.Material = b.Material INNER JOIN
     #T3 c on a.Plant = c.Plant AND a.Material = c.Material

Option 2: (In my head this logically should work but I haven't had to write this before so I don't know how practical it is)

SELECT a.Plant,
       a.Material,
       CASE WHEN Dmd > Avg AND Dmd > Fcst THEN Dmd ELSE
       CASE WHEN Avg > Fcst THEN Avg ELSE Fcst END END as 'Output'
FROM 
(SELECT Plant, Material, Dmd
FROM Table1) a
UNION
(SELECT Plant, Material, Avg
FROM Table2) b
UNION
(SELECT Plant, Material, Fcst
FROM Table3) c

If it helps, the entries in my input table should all be different ordering quantities for the same plant and material, so I know that the only entries in each query will be for the same plant and material as long as my Table1, Table2 and Table3 and joined to the input table.

EDIT: Examples of what might be in Tables1 2 and 3 as well as the desired output: (I'm sorry if the format is wrong, I'm trying)

Table1 (Plant, Material, Dmd)

1000   x   3

Table2 (Plant, Material, Avg)

1000   x   6

Table3 (Plant, Material, Fcst)

1000   x   4.5

Desired Output (Plant, Material, Output)

1000   x   6

Answer 1

You can do this with UNION then select the maximum using that as a sub-query e.g.

select max(v) from
(
    select Dmd as v from Table1
    union
    select [Avg] as v from Table2
    union
    select Fcst as v from Table3
) src;

Or you can use the INNER JOINS - which will handle this better if you are looking for a max per plant/material using a neat trick getting the various values into a sub-query e.g.

select a.Plant,a.Material, 
(
    SELECT Max(v) FROM 
    (VALUES (Dmd), ([Avg]), (Fcst)) AS valuestable(v)
) as [MaxV]
from @Table1 a
INNER JOIN @Table2 b ON a.Plant = b.Plant and a.Material = b.Material
INNER JOIN @Table3 c ON a.Plant = c.Plant and a.Material = c.Material;