Thanks Volker for the recommendation. I modified the query to use inner joins instead of the comma notation but the query plan still shows very poor estimate for RowsReturned for ltbl1 (34 vs 8936).

When I change all the joins in the above query to left outer joins, the query plan makes use of left outer nested loop joins and the join order is also different - ltbl1, ltbl3, ltbl2.

For the original query provided above with the commas replaced by inner joins, I see that the optimizer chooses to use nested loop joins and the join order is ltbl2, ltbl1, ltbl3. The table ltbl2 is about a half million and my understanding is that for each row in ltbl2 the largest table ltbl1 is parsed once which is worse.

Please help me understand how the optimizer makes use of statistics when joining table(s) and how it changes for different types of joins. The documentation mentions join statistics will be used which is a combination of the column statistics of the columns used in the join clause but doesn't explain exactly how it is used.

Is mixing left outer join with inner join a bad practice or is there a rule of thumb that should be followed that aids optimizer in making right decisions?

Thanks.

Is mixing left outer join with inner join a bad practice or is there a rule of thumb that should be followed that aids optimizer in making right decisions?

Mixing inner and left joins is surely very common practise. Personally, I mostly prefer to "start" the FROM clause with inner joins and then add left joins.

For further advice, it would be very helpful to show us HOW excalty your query looks like with inner join syntax and whether you use brackets or not. The statement "When I change all the joins in the above query to left outer joins" is too vague.

Thanks Volker for your feedback.

The below query using only left outer joins performs well and the optimizer estimate for RowsReturned too is approximately 80% correct(estimate 7054, actual 8936) for ltbl1:

SELECT ltbl2.col1,
       ltbl1.*,
       ltbl3.col1,
       ltbl3.col2,
       view1.col1,
       view1.col2,
       view1.col3
FROM   ltbl1 left outer join view1 on ltbl1.key1 =  view1.key
       left outer join ltbl3 on ltbl3.key = ltbl1.key2
       left outer join ltbl2 on ltbl2.key = ltbl1.key3
WHERE ( ltbl3.col1 not in ( 1,6 ) ) AND
      ( ltbl1.date_column is not null ) AND
      ( ltbl1.key2 = 600260151039) AND
      ( convert(varchar(1), ltbl1.col1) <> '5')

I tried modifying the original query to use inner joins instead of commas and the optimizer estimates remain poor(estimate 34, actual 8936):

SELECT ltbl2.col1,
       ltbl1.*,
       ltbl3.col1,
       ltbl3.col2,
       view1.col1,
       view1.col2,
       view1.col3
FROM   ltbl1 inner join ltbl3 on ltbl3.key = ltbl1.key2
       inner join ltbl2 on ltbl2.key = ltbl1.key3
       left outer join view1 on ltbl1.key1 =  view1.key
WHERE ( ltbl3.col1 not in ( 1,6 ) ) AND
      ( ltbl1.date_column is not null ) AND
      ( ltbl1.key2 = 600260151039) AND
      ( convert(varchar(1), ltbl1.col1) <> '5')

If I remove the ltbl2 from the above query, the estimates turn out to be better. I am not exactly sure how adding ltbl2 with an inner join is creating wrong/incorrect estimates for ltbl1.

Please let me know if more details are required.

Thanks.

As there is only one table to be outer-joined (view1) and there are no further joins on that, IMHO you could as well use

FROM   ltbl1
       left  join view1 on ltbl1.key1 = view1.key
       inner join ltbl3 on ltbl1.key2 = ltbl3.key
       inner join ltbl2 on ltbl1.key3 = ltbl2.key 

Unless you use brackets, joins are evaluated from left to right.

In the end, are the estimates a problem or the actual query performance?

In case the optimizer chooses poor plans continuously, as a last resort, you might also add an INDEX HINT to force the use of a particular key/index.