join vs where

1.
select ...
from T1 inner join T2 on T1.c=T2.c
where T2.c1 = '...'
2.
select ...
from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
Any reason not use the approach 2?nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
>
Compare the execution plans and query stats for each and see which looks
better.|||nick,
No differences when applying an inner join, except when grouping and using
"group by all". This behaviour is caused by the way SQL Server process the
query. Another different meaning when using outer join operators.
Example:
use northwind
go
select
a.orderid,
count(*)
from
orders as a
inner join
[order details] as b
on a.orderid = b.orderid
where
a.orderdate between '19980101' and '19981231'
group by all
a.orderid
select
a.orderid,
count(*)
from
orders as a
inner join
[order details] as b
on a.orderid = b.orderid
and a.orderdate between '19980101' and '19981231'
group by all
a.orderid
select
a.*
from
orders as a
left join
[order details] as b
on a.orderid = b.orderid
where
b.productid = 10
select
a.*
from
orders as a
left join
[order details] as b
on a.orderid = b.orderid
and b.productid = 10
go
AMB
"nick" wrote:

> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
>|||For an equi-join, I don't think it matters; however, a LEFT JOIN will
give you very different results (it would return all the rows from T1,
and only the rows from T2 that it could match and have valid data in
T2.c1).
Does that help?
Stu
nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?|||nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
From a technical point of view, they specify the same resultset.
However, I consider it a good practice to use the primary key/foreign
key columns in the JOIN clause, and all filtering conditions in the
WHERE clause.
This makes the query easier to read and understand, because it is in
line with the mental model of the query processing: the tables are
joined first. Only then are irrelevant rows filtered out. Also, it is
easier to understand, because the joins are usually pretty standard,
which means the real "work" is done in the WHERE clause.
So, asuming that the relation between table T1 and T2 is based on column
T1.c and T2.c, then I would never use syntax 2. I would change the
question: is there any reason not to use approach 1?
Gert-Jan