你可以几乎完全按照你写的那样实现这个逻辑，在case expression中：

case 
    when Type = 'EE' and Col1 = 'Red' AND Col2 = 'Yes' then 'Electric'
    when Type = 'MM' AND Col1 = 'Blue' AND Col2 = 'Yes' then 'Mechanic'
end

作为选择查询，请注意，这里应该为每个列引用包括表别名：

select
    t1.id, t1.type, t2.col1, t2.col2,
    case 
        when t1.Type = 'EE' and t2.Col1 = 'Red' AND t2.Col2 = 'Yes' then 'Electric'
        when t1.Type = 'MM' AND t2.Col1 = 'Blue' AND t2.Col2 = 'Yes' then 'Mechanic'
    end as NewColumn
from table1 as t1
inner join table2 as t2 on t1.id = t2.id