T-SQL: Optional table joins?

Let's say I have a stored procedure that will do complicated logic when the parameter @ComplicatedSearch = 1. When it is set to 1, I populate the variable table @ValidIds with valid rows that this procedure can return. When it's 0, the logic is bypassed and we don't need to filter out the rows to return.

So, past that logic, I end up with a statement like so:

SELECT
    m.*
    ,a.*
FROM
    MyTable m
    INNER JOIN AdditionalInfoTable a
       ON m.Id = a.MyTableId
WHERE
   (@ComplicatedSearch = 0 OR EXISTS(SELECT * FROM @ValidIds WHERE Id = m.Id))

This works fine; however, I believe it would be more efficient to join MyTable to @ValidIds when applicable opposed to using EXISTS(), especially when MyTable contains a large number of rows.

Is there any way of doing something like what I have below without writing multiple queries? (the actual query is very large, so having multiple versions with and without joins would not be ideal)

SELECT
    m.*
    ,a.*
FROM
    MyTable m
    ONLY DO THIS IF ComplicatedSearch = 1 PLEASE: INNER JOIN @ValidIds v
       ON m.Id = v.Id
    INNER JOIN AdditionalInfoTable a
       ON m.Id = a.MyTableId

Answers


Another option:

SELECT
   m.*     
  ,a.*
FROM MyTable m    
 INNER JOIN @ValidIds v
  ON m.Id = case
              when @ComplicatedSearch = 1 then v.Id  --  Filter rows
              else m.Id  --  Select all rows
            end
 INNER JOIN AdditionalInfoTable a    
  ON m.Id = a.MyTableId

You'd need to do performance testing to see if it's efficient enough. Some quick tests showed that (on my data) the same query plan was generated regardless of whether the first call was for complex or not complex.

The "bifurcated" approach (separate procedures) should be most efficient. However, having the same code with just a minor modification in two different places can be a major pain to support, particularly when you have to add subsequent changes to all "instances" of that code. And if the overall size of the data (e.g. overall performance) isn't too great, the "one size mostly fits all" approach might be most effective.


If you are after efficiency you should note that stored procedures will calculate the query plan on the first run then cache it and use the same one thereafter. In this case it means that it will choose to use @ValidIds depending on the first value of @ComplicatedSearch

Thus I would write the procedure more like

if @ComplicatedSearch = 1
   exec simple_search
else
   exec complex_search

Where simple_search includes your first query and complex_search also joins to @ValidIds

Yest you get the query twice but to get over that I would create a view

create view helper as
begin
SELECT
    m.*
    ,a.*
FROM
    MyTable m
    INNER JOIN AdditionalInfoTable a
       ON m.Id = a.MyTableId
end

and then simple selects from that view and complex joins to @ValidIds


Need Your Help

Playing .mp3 and .wav in Java?

java audio mp3 wav

How can I play a .mp3 and a .wav in my java application? I am using Swing, I tried looking on the internet, for like this example:

Castle Windsor: How do you add a call to a factory facility not in xml?

.net inversion-of-control castle-windsor

I know how to tell Castle Windsor to resolve a reference from a factory's method using XML, but can I do it programmatically via the Container.AddComponent() interface? If not is there any other ...

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.