Strange Problem With Left Join vs Subquery in Select

Question

I have a T-SQL query that is causing performance issues. Its a chunky one but the part that seems to be causing an issue is a simple LEFT JOIN.

This can be resolved by removing the left join and using a subquery in the select, however this seems unsatisfactory to me as I can't see why one works quickly and not the other.

Theres not a lot of data involved and there are keys/indexes on all the join columns. Only other thing I was wondering was about statistics on the database and whether they were affecting performance.

For instance (N.b. this is just a simplification of a much more complex query

SLOW

SELECT A.1,A.2,B.3 FROM A LEFT JOIN B ON A.ID = B.ID ...

FAST

SELECT A.1, A.2, (SELECT B.3 FROM B WHERE B.ID = A.ID) FROM A

Answer 1

these two queries may be not equivalent. If your subquery:

(SELECT B.3 FROM B WHERE B.ID = A.ID)

returns more than one row, your left join will just return two rows, but your subquery will blow up complaining that "subquery returned more than one row". If B.ID is unique, does the optimizer know it? Do you have a unique constraint or index on it?

Answer 2

In general a subquery would usually be slower than a left join, so there must be something else going on.

First show the whole query as the problem may be inteh part you used the ... to represent.

Next check your execution plans and see what the difference is. And are you absolutly sure there is an index on the id column. FK constraints do not automatically get indexes created.

Do both queries return the same records? One may be faster because it is not the equivalent of the other one.