x

AND CLAUSE IN "WHERE" vs JOIN

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.

more ▼

asked Jun 15, 2010 at 06:22 PM in Default

Bhaskar gravatar image

Bhaskar
333 16 17 20

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Jun 15, 2010 at 07:10 PM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

+1 for spelling out the best practice better than I did. I did not really mean to sound that I support comma-delimited list + where, stupid of me if my example made it look like I did.
Jun 15, 2010 at 07:20 PM Oleg
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.
Jun 16, 2010 at 02:57 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jun 15, 2010 at 06:53 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 16, 2010 at 08:29 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 15, 2010 at 06:59 PM

David 1 gravatar image

David 1
1.8k 1 3

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.
Jun 15, 2010 at 07:14 PM Oleg
@Oleg, Avoid =* or *= in any version. But putting any other predicate in the WHERE clause is perfectly OK. The WHERE clause is not going to be deprecated in any version!
Jun 15, 2010 at 07:43 PM David 1
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 :)
Jun 15, 2010 at 08:20 PM Oleg
"...the best practice is the best practice..." If only there was more agreement on what that actually means!
Sep 23, 2010 at 01:24 PM GPO
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x123

asked: Jun 15, 2010 at 06:22 PM

Seen: 3988 times

Last Updated: Jun 15, 2010 at 07:11 PM