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 |
|
jefals
Starting Member
8 Posts |
 Posted - 2011-04-27 : 21:58:15
|
| Hi,I find that I could code a join as either ... select ... from table1 t1 join table2 t2 on t1.a=t2.a and t1.b=t2.bor, I could code it as...select ... from table1 t1 join table2 t2 on t1.a=t2.a where t1.b=t2.bIs there any difference? Either in terms of what I get back from the select, or is there a difference in performance?Thanks,Jeff |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-27 : 22:17:49
|
| No and No to both your questions.BUT, if your joins were outer joins - left, right, or full - that would make a BIG difference.Take a look at these articles: http://www.sqlteam.com/article/additional-criteria-in-the-join-clausehttp://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx |
 |
|
|
jefals
Starting Member
8 Posts |
Posted - 2011-04-28 : 22:04:50
|
ok, got it Suni... Thanks. With 'and' on the join, if it were an outer join, you'd still be maintaining that outer join, but with the 'where', you'd be filtering out the results that don't match the condition on the where...One of those subtle "gotchas", that would have caught me at some point. ( And most likely still will -- sometime after I've long forgotten this article, and long before I become a well-experienced sql coder)  |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-28 : 23:11:55
|
in terms query behavior, both query will behave the same and give you the same result.But syntax wise, it is different. And you can't really "replace" the AND with WHERE. What you have here is placing the condition "t1.b = t2.b" in the ON clause or WHERE clause. For INNER JOIN, it behaves the same in either ON or WHERE but not necessary so for other type of JOIN.Maybe if you format the query this way, you can see it more clearlyselect ...from table1 t1 join table2 t2 on t1.a = t2.a and t1.b = t2.bwhere . . . select ...from table1 t1 join table2 t2 on t1.a = t2.awhere t1.b = t2.b. . .
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
jefals
Starting Member
8 Posts |
Posted - 2011-04-29 : 21:50:08
|
| yes, I've got it, Khtan -- thank you! |
|
|
|
|
|
|
|
|
join: "AND" vs "WHERE"