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.

Answers


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.


Need Your Help

Launch (:normal) command on vim start with .java files

vim execute autostart autocmd

I want to put line in my .vimrc file so that it will fold the /* ... */ like comments on autostart with *.java files.

Check for banned/inactive

php parameters actionscript-2

So I'm working on a virtual world and I am currently on the part where I am going to try to make the login.php check if the user is banned or not activated. If the user is banned, the banned column...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.