SQL Server Performance Forum – Threads Archive
Join Vs Where
Which is better using the inner join outer join etc. clause or performing thesame operation with where clause
If you want to retrieve matching rows from two tables, then use a join – this is preferred, eventhough you can do the same in a WHERE clause. In most cases, you use a WHERE clause for filtering, and you don’t need it for joining tables. When the match between rows is ‘funny’, then you can still do a lot of calculations in the ON part of a JOIN expression, so you would still do that in the join. You only use an outer join in cases where there may be no match on the outer table, and you still want to see the data from the inner table. If you want to see only matching rows, then use an inner join.
In case of INNER JOINs, it kind of depends on your personal preferences, since both ways are valid ANSI methods. In case of OUTER JOINs, use the ANSI way and do this in the JOIN clause. SQL Server 2005 will not support this deprecated JOINs in the WHERE clause anymore natively. Only in compatibility mode 80 and below this syntax is allowed. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
JOINs can get very resource intensive particularly if the tables you are performing a JOIN on are very large and/or you are joining multiple tables. Unless you are using a nested SELECT statement, you cannot perform the same functionality as a WHERE clause, because as Adriaan mentioned, a WHERE clause is simply a filter. Usually, it is better to use a JOIN over a nested SELECT. What is it exactly you are trying to achieve?
quote:Originally posted by Tahsin
JOINs can get very resource intensive particularly if the tables you are performing a JOIN on are very large and/or you are joining multiple tables. Unless you are using a nested SELECT statement, you cannot perform the same functionality as a WHERE clause, because as Adriaan mentioned, a WHERE clause is simply a filter. Usually, it is better to use a JOIN over a nested SELECT. What is it exactly you are trying to achieve?
What i want to know is whether join is better or where with respect to performance .
if the table is very large .i m only concern about performance
In case of INNER JOINs both approaches produce a logically identical result. Compare the execution plans. The SQL Server optimiser is smart enough to semantically rearrange your query and is likely to produce two identical execution plans. The JOIN method might be a tick faster during compilation time. But if that’s measurable at all, it’s neglectible. And since you have two identical execution plans, all else being equal, both methods will yield the same performance. As for OUTER JOINs, as I’ve already said, there is no alternative to JOINs anymore. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
]]>