1

In SQL Server, I'm creating a view that references several tables that should have the same row counts and primary keys, although different column data between them (hence the need to pull them all together for this particular view). I'm not always positive that they do though.

I know that the only way to be sure I have the number of rows I need is to use left joins (or outer joins), but I'm not sure whether there is a significant performance cost in doing so, and if so, how to quantify that.

Unfortunately, altering the tables is something I can't do, because an application relies on their structure being the way they are, so I'm using Views to pull the data together and I need to ensure I'm doing it the most efficient way possible.

Ian Joyce
  • 1,039
  • 1
  • 7
  • 12
  • I deleted my answer, as its main point was flat wrong. Sorry. – John Bollinger Jan 28 '15 at 21:43
  • Since you say they 'should' have the same row counts and primary keys; can't you 'enforce' this by adding a Foreign Key relationship from one table to the other? That way you KNOW (and hence the Query Optimizer too!) that for every record in one table there will also be a matching record in the other table. That way you don't need to add 'built-in safety mechanisms' in your code to deal with /dirty data/. And I'm guessing that if the optimizer finds an OUTER query on a relation that has a FK on it, it will use the algorithm most appropriate from the OUTER & INNER JOIN toolbox as it pleases. – deroby Jan 29 '15 at 11:53
  • That would certainly be a good way to handle it, but unfortunately that would require reprogramming the application (which I'm not involved with) to handle exceptions from the constraint, or ensure that such an occurrence wouldn't happen in the first place. – Ian Joyce Jan 29 '15 at 21:11

1 Answers1

1

Yes, there is a performance difference between inner and outer joins. They are different operations so they have different underlying algorithms. The answers to this question pretty much explain this.

Whether the performance difference matters is another question. With data that always matches (so the results are the same), I would expect the performance of left outer join be pretty similar to inner join, particularly if the data structures are optimized for the query. However, my expectations are no guarantee. If you really care about this, you should test the two different versions of your query on your data in your environment.

The results of such a test don't guarantee the results in all cases. They do all you to make an informed decision about the cost of using left join rather than inner join.

Community
  • 1
  • 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for that. It does seem like the performance is similar on smaller queries I've put together and the query plan suggests a Merge Join is used whether I use an inner join or an outer join. I guess as you say, I'll just have to test the difference in the giant query I need for this particular view to see. – Ian Joyce Jan 28 '15 at 21:38
  • @IanJoyce . . . In a `left join`, the query plan is going to (tend to) scan the first table. If the query plan for the `inner join` does the same thing, then the performance should be similar. – Gordon Linoff Jan 29 '15 at 03:09
  • Thanks for your response Gordon! – Ian Joyce Jan 30 '15 at 00:07