AND CLAUSE IN "WHERE" vs JOIN

It is best practice to use JOIN to specify the join conditions

BOL:

Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins

In most cases it will make the query clearer as to what the JOIN conditions are, rather than what the WHERE filter is specified as.

Execution plans will (as others have said) hardly differ, but your understanding of the query might.

General rule is if you need data from the join of 2 or more tables then it is better to join tables on key columns and then optionally specify additional predicates. That said, the engine will optimize query and will usually restate your comma-delimited list of tables and where clause as a join. Consider, for example following 2 select statements, first one written intelligently and the second - old style:

use AdventureWorks;
go

select
    c.FirstName, c.LastName, c.EmailAddress,
    emp.Title Position, emp.BirthDate, emp.Gender
    from Person.Contact c inner join HumanResources.Employee emp
        on emp.ContactID = c.ContactID
    where c.LastName between 'A' and 'C'
    order by c.LastName, c.FirstName;

select
    c.FirstName, c.LastName, c.EmailAddress,
    emp.Title Position, emp.BirthDate, emp.Gender
    from HumanResources.Employee emp, Person.Contact c
    where 
        emp.ContactID = c.ContactID 
        and c.LastName between 'A' and 'C'
    order by c.LastName, c.FirstName;

If you highlight both selects in SSMS query window and press Ctrl + L (show estimated execution plan) you will see that both plans are identical. Nevertheless, I personally don't like old style syntax, and therefore, I will never lower myself down enough to consider using it, much less to actually use it. Even if I need a cartesian product of 2 tables, I will still prefer from table1 cross join table2 rather then from table1, table2.

Just my 2 cents.

Oleg

Just to add an additional note. As the others have ably pointed out, with the exception of OUTER JOINs, there is little difference, in most circumstances, between placing the filtering criteria in the ON or the WHERE and there is little difference in placing the joining criteria in the ON or the WHERE. That rule largely holds true with simple queries. As queries get more complicated though, the ability of the optimizer to rearrange your join and filter criteria degrades simply because it doesn't have time to work through as many permutations. With that in mind, you are helping the optimizer by putting your join criteria in the ON clause and your filtering criteria in the WHERE clause, as appropriate.

Plus, long term maintenance is easier if you seperate the ON & WHERE into join and filter information. If you move on, up, or away, from the code you've created, the next person reading it should be able to readily understand, logically, what is in place in your queries.

Logically speaking the ON clause is evaluated before the WHERE clause but that doesn't actually make any difference unless the join is an OUTER one. Use whichever style makes the meaning clearer.