Is it better to do an equi join in the from clause

Question

When joining two simple tables on primary key columns and placing an addition equality condition this can be done in the join itself or the where clause.

For instance the following are equiavalent. My question is - is there any reason to use one style over the other?

SELECT * 
FROM A
INNER JOIN B ON A.A_ID = B.A_ID
            AND A.DURATION = 3.00

...vs:

SELECT * 
FROM A
INNER JOIN B ON A.A_ID = B.A_ID
WHERE A.DURATION = 3.00

Answer 1

For SQL Server 2000+, the query plans for each of these queries will be indentical, and therefore so will the performance.

You can verify this by using SSMS to display the actual execution plan for each of the queries hitting CTRL+M before executing your query. The results pane will have an additional tab that shows you the execution plan. You'll see that in this case, the two plans are the same.

Answer 2

It's a style matter. Generally, you'd want to put the conditions that define the "shape" of the result set in the FROM clause (i.e. those that control which rows from each table should join together to produce a result), whereas those conditions which filter the result set should be in the WHERE clause. For INNER JOINs, the effects are equal, but once OUTER JOINs (LEFT, RIGHT) are involved, it feels a lot clearer.

---

In your first example, I'm left asking "what has this got to do with Table B?" when I encounter this odd condition in the JOIN. Whereas in the second, I can skip over the FROM clause (and all JOINs) if I'm not interested, and just see the conditions which determine whether rows are going to be returned in the WHERE clause.

Answer 3

It's the type of JOIN that matters.
There's no difference, either version of the query provided will use the same execution plan because you are dealing with an INNER JOIN.

If dealing with an OUTER JOIN (IE: LEFT, RIGHT), there is a huge difference between the two versions because the WHERE criteria is applied after the JOIN is made. If the criteria is specified in the ON clause, the criteria is applied before the JOIN is made which can made a considerable difference between the result sets.

Answer 4

Generally speaking it makes no semantic difference.

There is one edge case where it can do though. If the (deprecated) GROUP BY ALL construct is added to the query as illustrated below.

DECLARE @A TABLE(A_ID INT, DURATION DECIMAL(3,2) )
INSERT INTO @A VALUES(1,2.00)

DECLARE @B TABLE(A_ID INT)
INSERT INTO @B VALUES(1)

/*Returns one row*/
SELECT *
FROM @A A
INNER JOIN @B B ON A.A_ID = B.A_ID
WHERE A.DURATION = 3.00
GROUP BY ALL A.A_ID, A.DURATION, B.A_ID

/*Returns zero rows*/    
SELECT *
FROM @A A
INNER JOIN @B B ON A.A_ID = B.A_ID  AND A.DURATION = 3.00
GROUP BY ALL A.A_ID, A.DURATION, B.A_ID

Answer 5

Its a matter of style. The optimizer will do what's best.

Answer 6

    --Samples for time of join wheather can we write condition at where or ON 
create table #emp(id int ,teamid int)
create table #team(tid int,name char(2))
insert into #emp values(1,1)
insert into #emp values(2,1)
insert into #emp values(3,2)
insert into #emp values(4,0)

insert into #team values(1,'A')
insert into #team values(2,'B')
insert into #team values(3,'C')

--select * from #emp
--select * from #team

--on inner join => there is no difference in Query Exc. Plan
--select * from #emp e join #team t on e.teamid=t.tid where e.teamid=2
--select * from #emp e join #team t on e.teamid=t.tid and e.teamid=2


/*on outetr join see the differnence If dealing with an OUTER JOIN (IE: LEFT, RIGHT), 
there is a huge difference between the two versions because the WHERE criteria is applied after the JOIN is made. 
If the criteria is specified in the ON clause, the criteria is applied before the JOIN is made which can made a considerable difference 
between the result sets.*/
select * from #emp e left join #team t on e.teamid=t.tid
select * from #emp e left join #team t on e.teamid=t.tid where e.teamid=2
select * from #emp e left join #team t on e.teamid=t.tid and  (e.teamid=2 or t.tid=1) and t.name='A'


drop table #emp
drop table #team