sql - Is it better to do an equi join in the from clause or where 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

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