tsql - SQL server select from one of two possible tables -

i have 2 tables, current table , archive table.

when searching record, don't know table in, have query following:

select mything (current union archive) 

i know, possible write above query like

select mything current  if mything null begin     select mything archive end 

and which, if either, or these approaches more performant.

your second example work is. needs variable

declare @thething varchar(max)  select @thething = mything current  if (@thething null) begin     select @thething = mything archive end  -- 'output' value select @thething 

you stick code in stored procedure or function or something.

note sure whether faster union or not. hunch above faster if mything indexed in both tables. thats guess.

edit: performance affected how thing found in first table. above code faster union if target found in first table majority of time.

edit: martin points out, worth if there's guaranteed 1 match in 2 tables. otherwise, need union.