What is better and why? Is it something like query optimizer first works on join then uses filters in where clause. or it gets record form individual tables then uses joins to merge them.
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.
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.
It is best practice to use JOIN to specify the join conditions
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.
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.
No one has followed this question yet.