0

Let's say you are doing a count query that contains a left join. Let's also say that we know for a fact that the left join will return at most 1 row (for each outer row). From this, we can conclude that the left join cannot affect the count, hence it is unnecessary to perform the left join. In SQL Server, is it possible to express that a left join can result in at most 1 row being returned, thereby allowing the optimiser to skip the left join for a count query? I know you can do it with an outer apply, but I was wondering if you could do with with a plain left join. In my particular case, not performing this left join for the count query saves a lot of time.

Outer Apply example

Select count(*)
from table1 a
outer apply ( select top 1 * from table2 b where a.id = b.id) q

The optimiser is smart enough to not perform the outer apply. My question is, can you also do this with a plain left join?

14
  • Why do the LEFT JOIN at all if you're not using that table anyway?
    – J.D.
    Jan 22 at 1:44
  • The query retrieves the data to be displayed in a user interface grid. I call the query once to get the paginated rows to be displayed, and then I call it again to get the total count of rows (to display in the bottom right of the grid). The data query requires the left join, the count query does not. Jan 22 at 1:49
  • "The data query requires the left join, the count query does not." - Right, and the count query is the one you're trying to add a hint to. But why not just not do the LEFT JOIN in that instance?
    – J.D.
    Jan 22 at 4:51
  • 1
    "So duplicating code is the right way to do things?" - Yes, in the database layer, for the tradeoff of maximizing performance, it's commonly accepted that duplicating code is a correct design pattern.
    – J.D.
    Jan 22 at 5:51
  • 1
    Does table2 have a unique constraint on id? Jan 22 at 15:49

1 Answer 1

0

Not directly, a left join will always return all matches from the joined table/view.

Less directly, you could use a CTE with the row_number window function and left join to that with a filter on rownumber=1. This may be more efficient than your outer apply depending on other parts of the query but you'll need to look at plans produced an IO stats of each option with realistic data to be sure either way.

2
  • 1
    The optimiser isn't smart enough to know that rownumber=1 results in at most 1 joined row per outer row. Jan 22 at 2:02
  • @user23278799 - no, but such a CTE based query may result in seeking based on other filters (maybe still followed by a partial scan) or a scan down a relevant index, in each case due to predicate push-down into the CTE, where OUTER APPLY could result in the correlated query being run once per matching row. In some cases this might be far more efficient. Of course the opposite may be better in some queries too, hence I pointed out the need to compare plans and IO stats with real data. Jan 23 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.