SQL - Temporary variable inside SELECT -

so found myself doing this:

select     foo =      case         when a.foo null (select x.foo x x.id = a.id)         else a.foo     end,     bar =     case         when a.bar null (select x.bar x x.id = a.id)         else a.bar     end,     alice =      case         when a.alice null (select x.alice x x.id = a.id)         else a.alice     end inner join b on a.x = b.x 

that doesn't pretty. wondering whether there way create temporary variable such as:

with temp select * x x.id = a.id 

inside scope of select , refer it?

try

select         foo   = coalesce(a.foo, x.foo),        bar   = coalesce(a.foo, x.foo),        alice = coalesce(a.alice, x.alice)   join         b on a.x = b.x left join        x on x.id = a.id 

or

select         coalesce(a.foo, x.foo) foo,        coalesce(a.foo, x.foo) bar,        coalesce(a.alice, x.alice) alice   join         b on a.x = b.x left join        x on x.id = a.id