Why does a SQL join choose a sub-optimal query plan?

Question

Ok so I realize that this is a pretty vague question, but bear with me.

I have experienced this problem on numerous occasions with different and unrelated queries. The query below takes many minutes to execute:

SELECT <Fields>
FROM <Multiple Tables Joined>
    LEFT JOIN (SELECT <Fields> FROM <Multiple Tables Joined> ) ON <Condition>

However, by just adding the join hint it query the executes in just seconds:

SELECT <Fields>
FROM <Multiple Tables Joined>
    LEFT HASH JOIN (SELECT <Fields> FROM <Multiple Tables Joined> ) ON <Condition>

The strange thing is the type of JOIN specified in the hint is not really what improves the performance. It appears to be because the hint causes the optimizer to execute the sub query in isolation and then join. I see the same performance improvement if I create a table-valued function (not an inline one) for the sub-query. e.g.

SELECT <Fields>
FROM <Multiple Tables Joined>
    LEFT JOIN dbo.MySubQueryFunction() ON <Condition>

Anybody have any ideas why the optimizer is so dumb in this case?

Answer 1

If any of those tables are table variables, the optimizer uses a bad estimate of 0 rows and usually chooses nested loop as the join technique.

It does this due to a lack of statistics on the tables involved.