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.
asked
AND CLAUSE IN "WHERE" vs JOIN
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
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.
This does not make any difference only because of the courtesy of the database engine which is capable of figuring out how to rearrange the predicate conditions without disturbing desired results. If you have compat 80 server then I suppose that it is still OK to attempt =* (or *=) syntax for outer joins, but this will not work in compat 90 or better and therefore, it looks like the time has come to part with comma-delimited list + where syntax.
I have been avoiding those for a long time now. All I wanted to point out is that the best practice is the best practice, that is all. In other words, I always opt to use the ANSI compliant JOIN syntax with key columns going into ON and whatever (if anything) into WHERE. This way the conditions are clearly separated and I don't have to rely on the mercy of the engine. I remember good old times when there was a nasty side effect in Oracle engine which lead it to read where conditions from bottom to top if all were AND conditions. This could lead to some slooow queries occasionallly :)
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.
Very well said, Kev! Just to reemphasize, the distinction is almost entirely for human benefit, and has virtually no impact on the optimizer (save for outer joins). But readability is vital for maintainability so it is well worth spending some time on.
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.
