Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Van
Constraint Violating Yak Guru
462 Posts |
 Posted - 2009-04-27 : 17:03:15
|
| I know it's best to join tables in the JOIN section and filter in the WHERE section. I can't remember why it's better to filter in the WHERE section and can't seem to find much on it.For example:select *FROM tableA a join tableB b on a.id = b.id WHERE b.name = 'test'is better thanselect *FROM tableA a join tableB b on a.id = b.id and b.name = 'test'Can someone explain why the first is better and what the differnces are/could be?Thanks |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-04-27 : 17:22:12
|
| The optimizer is generally smart enough to figure out these kinds of things. So, you may not see a difference, especially for small queries like you example. However, when you are doing lots of joins to larger tables (100's of millions of rows) sometimes putting the restriction on the join might make a difference. Say, for example, you were joining 10 tables with millions of rows each and one of those tables had 600 million rows and only 10 of those rows matched the criteria "name = 'test'." If you apply the restriction on the join then the following joins would only need to match up with those 10 rows instead of matching to the 600 million and then applying the filter at the end. But, again, the optimizer might be able to figure this out for you.I like to restrict the data as soon as possible in my code, so I tend to place the restrictions on the JOIN condition rather than the WHERE clause.The other, more significant, issue is when you are using OUTER JOINS (LEFT, RIGHT, FULL). Where you place the restriction can cause your results to be drastically different.As to why the first is better?.?.... I don’t think it is better. But, some people would be more comfortable with it and/or find it easier to read. |
 |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-04-28 : 10:01:39
|
| Thanks for the info."The other, more significant, issue is when you are using OUTER JOINS (LEFT, RIGHT, FULL). Where you place the restriction can cause your results to be drastically different."This may have been what I was trying to think of...using it with OUTER JOINS and causing issues. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-04-28 : 11:40:25
|
| Here is a link to a sample I have provoded in the past that helps illistrate:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98481#374748[/url] |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-04-28 : 12:07:35
|
| Yea it was definitely the OUTER JOIN thing I was thinking of. If it's an INNER JOIN it doesn't really matter if the criteria is in the JOIN or WHERE. |
|
|
|
|
|
|
|
|
JOIN vs WHERE question