Best left outer join method 1

[jondow]

Hi,

Can anyone tell me which of the following is the best/most efficient way of performing a left outer join and what the differences are?

SELECT
	a.a_name
	q.q_text
FROM
	question q
	LEFT OUTER JOIN answer a ON a.q_ID = q.q_ID and a.a_valid = 1

SELECT
	a.a_name
	q.q_text
FROM
	question q
	LEFT OUTER JOIN (SELECT a_name, a_ID FROM answer WHERE a_valid = 1) a ON a.q_ID = q.q_ID

I had always assumed the first would be fastest

Thanks

Rick

 

[Qik3Coder]

Look at it this way.

With the first you are using the act of joining, to slim results from Table 1.
With the second you slim the results with a query, that may hit a index). Then the slimmed results are tied to the actually query, with the join.

I have had a couple cases with large results or complex queries where it was easier to pull the data out into a temp, like that, then join to it.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[gmmastros]

best/most efficient

The answer is: it doesn't matter.

When you run a query, SQL Server first creates an execution plan, and then it uses the execution plan to execute the query. In this case, both queries will have the same execution plan. Of course, this is a relatively simple example, using just 2 tables. More complicated queries can produce different execution plans while returning the exact same data. To see which is the most efficient, I usually put both queries in the same query window, turn on the 'Actual Execution Plan' and then run the query.

Basically, to answer your question, SQL Server is smart enough to generate the BEST/same execution plan for your query.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hwkranger]

George is right, SQL server is smart enough to know the difference.

From your stand-point and knowing that SQL will almost 100% optimize the execution of your SQL regardless of how you write it, you should focus on readability.

The first is much easier to read (atleast to me).

 

[jondow]

Thats great. Thanks for your comments!

Rick