4 ms·

Technically the sub select is a semi join which could be optimized differently than left join (and could give different results). If bar.id had a unique constra

• tyfon 10y ago
  Well, I simplified the example a bit, for the real query bar.id is not unique and has to be constrained by other means. Think an application/case with a table with credit scoring variables where each variable has a case id and a variable type id, so i had to join the same table multiple times with different criteria. But replacing all of the left joins here with subselects did reduce the query time by an insane amount. However I have seen this behaviour with unique indexed left joins too just not so extreme. And for comparison (i have the same schema in my analytics postgresql database), the exact same query here takes about 1.5 minutes with left joins.
  • dsp1234 10y ago
    The two statements, as mentioned by gfody, are not equivalent. For the first statement, there can be any number of output rows based on the join condition. For the second statement, by definition, there can only be output row per input row in foo. That's a lot more information that the query planner can use to make optimizations.
    • tyfon 10y ago
      The table _does_ have a unique index on the combination of caseid and type, but I'm not sure if that is relevant.
      • dsp1234 10y ago
        It's possible, as the other commenters mentioned, it would come down to reviewing the query plan. I was just trying to address the two parts of your original comment, which were "is very baffeling" and "left join performance in sql server is very bad in some situations.". Hopefully my answer helped to un-baffel a little.