SQL server select from one of two possible tables
I have two tables, a current table and an archive table.
When searching for a record, I don't know which table it will be in, so I have a query something like the following:
SELECT myThing FROM (current UNION archive)
I would like to know, is it possible to write the above query as something like
SELECT myThing FROM current IF myThing IS NULL BEGIN SELECT myThing FROM ARCHIVE END
and which, if either, or these approaches is likely to be more performant.
Your second example will almost work as it is. Just needs a variable
Declare @theThing varchar(max) SELECT @theThing = myThing FROM current IF (@theThing IS NULL) BEGIN SELECT @theThing = myThing FROM ARCHIVE END -- to 'output' the value SELECT @theThing
You could then stick that code in a stored procedure or function or something.
Note sure whether it will be faster than the union or not. My hunch would be the above would be faster if myThing is indexed in both tables. But thats just a guess.
edit: Performance will also be affected by how often the thing is found in the first table. Above code will likely be faster than a union if the target is found in the first table the majority of the time.
edit: As Martin points out, all this is only worth it if there's guaranteed to be only one match in the two tables. Otherwise, you need a Union.