CODEWITHSUNDEEP
sql-server-2005
kacalapy
kacalapy

Reputation: 10154

best sql2005 performance: sub queries vs joins?

whats better in terms of SQL server efficiency, to use sub queries instead or joins?

I know uncorrelated are better than correlated sub-queries. But what about joins?

The SQL becomes more readable and understandable using joins OUTER JOIN and check for NULLS but is it worse or better for performance of the DB?

Upvotes: 0

Views: 404

Answers (2)

Martin Smith
Martin Smith

Reputation: 453687

It's difficult to answer a question as abstract as this but in the obvious cases I can think of where I would need to choose between the two then sub queries.

These cases are NOT EXISTS vs OUTER JOIN and NULL and IN/EXISTS vs JOIN and DISTINCT. The sub queries do appear in the plan as JOINS anyway.

(Edit: Just noticed that the second of my examples is mentioned in your question)

Upvotes: 1

HardCode
HardCode

Reputation: 6766

You'll find that by using JOINs, the query optimization engine in SQL Server can formulate a more efficient query execution plan. As a rule, you are better off always using JOINs, but if there is a performance problem, try to rework the query and document performance differences. There are always very strange exceptions.

Take for example my query I was working on yesterday - by adding a ORDER BY to the query, it ran faster than without the ORDER BY. What the heck? How can that be? It seems to go against the very concept of SQL, since every operation has a time cost. However, the query execution plans were created completely different by SQL Server 2000 with and without the ORDER BY. Go figure! It points out the importance of checking execution plans and monitoring query performance.

Upvotes: 3

Related Questions