Most efficient inner join

Question

Post reply

Most efficient inner join

Jamie Thomson

SSChampion

Points: 11805
More actions
October 26, 2004 at 4:55 pm

#66634

Hello,
Can someone tell me if the query processor will produce the same query plan for the following 2 queries? In other words...which one (if either) runs most efficiently?
Query 1:
SELECT <column_list>
FROM a INNER JOIN b
ON a.col1 = b.col1
WHERE b.col2 = <some_value>

Query 2:
SELECT <column_list>
FROM a INNER JOIN b
ON a.col1 = b.col1
AND b.col2 = <some_value>

Also, does the same answer (whatever the answer may be) apply for outer joins as well?

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

You will have to test it yourself in Query Analyzer by producing the plans and studying them.

Kindest Regards,

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

Most likely the answer is yes for equi joins - there is no difference in having the second criteria in the ON clause or the WHERE clause.

For outer joins the answer is/may be no. In outer joins it depends if filtering is applied on the inner or outer table. As a result of that, filtering in the ON clause or the WHERE clause produces different results, thus also different plans. For outer joins, it's not a question of efficiency, it's a question of what you 'really ask'.

As noted, though - the best way is to review the plans yourself.

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

If you are subscribed to SQL Server Mag you should have access to this interesting article by Itzik Ben-Gan:

http://www.winnetmag.com/Article/ArticleID/40621/40621.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 4

In the INNER JOIN query in your post the results and efficiency are exactly the same. However in some cases when you get into more complex INNER JOINS moving some where conditions to a particular INNER JOIN can help because of the way joined data is stored at each interm level.

Sorry no example.

Now for an outer join it depends on the net effect you want to have.

For example if you have a table of customers and you LEFT JOIN to the orders but only want to see the customers who's order was for Beer and you put in the WHERE cluase where order = beer then you need to rethink your join as an INNER JOIN is the net effect. However if you only wanted to see Orders that were beer but all cutomers then you have to put the condition of order = beer in the JOIN of customers to Orders. Many people make the mistake of where to put the condition. Basically remember, if you use and OUTER JOIN and place the side in which only matches are found condition in the WHERE clause your net effect is always that of an INNER JOIN, if you place in the ON clause you enforce the join as you requested.

That said if you want to see only the records for last_name = smith then you place that in the WHERE cluase. If you however wanted all customer records and only the records where last_name = smith placed an order then you palce the condition in the ON cluase of the join to Orders.

OUTER JOIN condition placements are all about what you want the next effect to be.