Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?

[=?ISO-8859-1?Q?Ren=E9?=]

Hi,

is there a rule of thumb what is better/faster/more performant in SQL
Server 2005?

a) SELECT * FROM A INNER JOIN B ON B.ID = A.ID AND B.Cond1 = 1 AND
B.Cond2 = 2
b) SELECT * FROM A INNER JOIN B ON B.ID = A.ID WHERE B.Cond1 = 1 AND
B.Cond2 = 2

This is a very simple sample. I often have cases with three or more
INNER JOINs each of them having different conditions. Logically I'd
say that putting the conditions to the JOIN statement is faster as it
reduces the amount of joined data whereas b) would join everything and
then sort out those not matching the WHERE conditions.

René

[Plamen Ratchev]

The SQL Server query optimizer is free to move expressions up and down
within a query plan to achieve cost optimized plan for retrieving data.
For INNER JOIN it is very typical to move predicates between the JOIN
and WHERE clauses and you will end up with the same execution plan for
both queries.

--
Plamen Ratchev
http://www.SQLStudio.com

[Gert-Jan Strik]

René wrote:

>
Hi,

is there a rule of thumb what is better/faster/more performant in SQL
Server 2005?

a) SELECT * FROM A INNER JOIN B ON B.ID = A.ID AND B.Cond1 = 1 AND
B.Cond2 = 2
b) SELECT * FROM A INNER JOIN B ON B.ID = A.ID WHERE B.Cond1 = 1 AND
B.Cond2 = 2

This is a very simple sample. I often have cases with three or more
INNER JOINs each of them having different conditions. Logically I'd
say that putting the conditions to the JOIN statement is faster as it
reduces the amount of joined data whereas b) would join everything and
then sort out those not matching the WHERE conditions.

René

As mentioned by Plamen, from a performance perspective, there is no
difference.

I have come to the conclusion that I my preference is to only list the
foreign key column(s) in the ON clause, and all other filters in the
WHERE clause. This approach makes the query easy to read, and the "real"
filtering condition at all in the lower part of the query.

Of course, for Outer Joins it is a different story...

--
Gert-Jan
SQL Server MVP

[--CELKO--]

>>. Logically I'd say that putting the conditions to the JOIN statement is faster as it reduces the amount of joined data whereas b) would join everything and then sort out those not matching the WHERE conditions. <<

For INNER JOINs it does not matter. The optimizer will re-arrange
things and come up with the same execution plan in 99.999% of the
time.

What matters is how easy it is to maintain the code. ACCESS
programmers and other people who have worked with file systems like to
put the join conditions in the ON clauses and the filters in the WHERE
clause. This lets them imagine pairwise tape merges done in sequence
before the final step to get the report out.

Older SQL programmers tend not to use the INNER JOIN syntax at all
because it lets us see n-ary relationships in the WHERE clause and we
can imagine a more general approach to data than a simply sequence of
binary operators.

That mindset is a little hard to explain, but it like someone who
thinks of a chain of +'s and someone who thinks of "Big Sigma" when
they do a summation.

[Ed Murphy]

--CELKO-- wrote:

What matters is how easy it is to maintain the code. ACCESS
programmers and other people who have worked with file systems like to
put the join conditions in the ON clauses and the filters in the WHERE
clause. This lets them imagine pairwise tape merges done in sequence
before the final step to get the report out.

Older SQL programmers tend not to use the INNER JOIN syntax at all
because it lets us see n-ary relationships in the WHERE clause and we
can imagine a more general approach to data than a simply sequence of
binary operators.

In situations where n-ary relationships are the most intuitive way of
expressing the intended concept (e.g. 'x between y and z'), that's
fine. If your joins are just foreign key references (the ones I
encounter in practice usually are), then I prefer to put the join
conditions in the ON clauses to make it harder to omit one by mistake.

[--CELKO--]

>In situations where n-ary relationships are the most intuitive way of expressing the intended concept (e.g. 'x between y and z'), that's fine. *If your joins are just foreign key references (the ones I encounter in practice usually are), then I prefer to put the join conditions in the ON clauses to make it harder to omit one by mistake. <<

BETWEEN-ness is one n-ary, but the imagine a typical multi-table
situation where each table joins to more than one other table.
Because of the distance among the ON clauses, it is hard to see that
we need a search condition that jumps back7 12, and 15 lines of code
for its tables. But when i have a WHERE clause list, I can sort all
of the searches that deal with one table together as a sequence of
lines.

We found out that just simple "pretty printing" saved 8-12%
maintenance time in COBOL back in the 1980's.

[bill]

I hated the "new" syntax for a long time because I was used to the
older syntax. However, I have gotten used to the newer syntax and
find I like it quite a bit with join conditions in the ON clause,
especially for outer joins. I agree with Joe's point that formating
is important, and I don't like the fact that many tools uglify the
code.

As far as I know, SQL Server requires you to include the "and column x
is null" (see my second example) when doing an outer join with the
join conditions in the WHERE clause. Oracle has a special notation so
you don't have to test for the null condition, but I don't think SQL
2008 does. Even in Oracle, I don't like that special notation.

Putting all join conditions in the ON clause is also nice for testing
large queries. If you are working against a poorly documented and/or
poorly designed schema (most commercial schemas are both), you often
have to piece together a query and keep an eye on the result set as
you design. It's easier to comment out a join where the table name
and conditions are all close together.

Thanks,

Bill

--Join in the ON clause (the lack of a column list [ * ] is just for
economy of typing)
select
*
from
TABLE_A
left outer join
TABLE_B
on TABLE_A.COLUMN = TABLE_B.COLUMN
and TABLE.COLUMN_02 = 'X'

----------------------------

-- Join Conditions in the WHERE clause
select
*
from
TABLE_A
left outer join
TABLE_B
where TABLE_A.COLUMN = TABLE_B.COLUMN
and TABLE.COLUMN_02 = 'X' or TABLE_B.COLUMN_02 is null

[Erland Sommarskog]

bill (bi**********@gmail.com) writes:

As far as I know, SQL Server requires you to include the "and column x
is null" (see my second example) when doing an outer join with the
join conditions in the WHERE clause.

It's not SQL Server that requires you, but the whole logic of it.

FROM JOIN ... builds a table, which is then filered by the WHERE
clause. So if you have "A LEFT JOIN B", and have a condition on a
column in B in the WHERE clause, you are effectively filter out
all rows where B.col has a NULL value. Unless you add that extra
condition.

Oracle has a special notation so you don't have to test for the null
condition, but I don't think SQL 2008 does. Even in Oracle, I don't
like that special notation.

I don't know what Oracle has, but it sounds horrible.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[bill]

I worded that section of my post poorly. I agree that logic dictates
inclusion of the test for NULLs in an outer join with the conditions
in the WHERE clause. The special notation in Oracle is basically
their own syntax that combines the null test with the regular
condition. I don't like it for probably the same reason as you. I
generally don't like "super operators" that do multiple things.

OTOH, I think SQL server tends to carry the "just give the users the
primitives and let him build what he needs" concept too far when it
comes to built in functions. Oracle has a lot of neat functions that
you have to build in SQL server. One of the most useful that comes to
mind is MINUS. Can't think of others at the moment, but I get annoyed
when want to do something that is simple with a built in oracle
function and I have to write a proc with SQL Server. On the whole
though, SQL Server is a fantastic product.

Thanks,

Bill
On Nov 4, 3:28 pm, Erland Sommarskog <[e-mail]:

bill ([e-mail]) writes:

As far as I know, SQL Server requires you to include the "and column x
is null" (see my second example) when doing an outer join with the
join conditions in the WHERE clause.

It's not SQL Server that requires you, but the whole logic of it.

FROM JOIN ... builds a table, which is then filered by the WHERE
clause. So if you have "A LEFT JOIN B", and have a condition on a
column in B in the WHERE clause, you are effectively filter out
all rows where B.col has a NULL value. Unless you add that extra
condition.

Oracle has a special notation so you don't have to test for the null
condition, but I don't think SQL 2008 does. Even in Oracle, I don't
like that special notation.

I don't know what Oracle has, but it sounds horrible.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

bill (bi**********@gmail.com) writes:

OTOH, I think SQL server tends to carry the "just give the users the
primitives and let him build what he needs" concept too far when it
comes to built in functions. Oracle has a lot of neat functions that
you have to build in SQL server. One of the most useful that comes to
mind is MINUS.

I may be entirely off-base, but I seem to recall that MINUS is the
same as the EXCEPT operator in SQL Server. (And EXCEPT is the ANSI
standard.)

But it is true, that Oracle has some constructs that are sorely
lacking from SQL Server. The most important is further extentions
to the OVER clause that permits you to easily and efficiently
implement running aggregates and sliding windows. And which are in
ANSI.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DA Morgan]

Erland Sommarskog wrote:

bill (bi**********@gmail.com) writes:

>OTOH, I think SQL server tends to carry the "just give the users the
primitives and let him build what he needs" concept too far when it
comes to built in functions. Oracle has a lot of neat functions that
you have to build in SQL server. One of the most useful that comes to
mind is MINUS.

I may be entirely off-base, but I seem to recall that MINUS is the
same as the EXCEPT operator in SQL Server. (And EXCEPT is the ANSI
standard.)

But it is true, that Oracle has some constructs that are sorely
lacking from SQL Server. The most important is further extentions
to the OVER clause that permits you to easily and efficiently
implement running aggregates and sliding windows. And which are in
ANSI.

MINUS is a set operator similar to INTERSECT, UNION, and UNION ALL
which, as you point out, is equvalent to EXCEPT in SQL Server.

I can appreciate some of Bill's frustration when it comes to things
such as analytic functions and regular expressions but in set operators
SQL Server and Oracle are equivalent.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
da******@x.washington.edu (replace x with u to respond)

[Gints Plivna]

On 7 Nov., 23:09, DA Morgan <[e-mail]:

MINUS is a set operator similar to INTERSECT, UNION, and UNION ALL
which, as you point out, is equvalent to EXCEPT in SQL Server.

I can appreciate some of Bill's frustration when it comes to things
such as analytic functions and regular expressions but in set operators
SQL Server and Oracle are equivalent.

Yeahh and hopefully they will introduce also INTERSECT ALL and EXCEPT
(MINUS) ALL.

Gints Plivna
http://www.gplivna.eu

[Gints Plivna]

On 4 Nov., 09:21, bill <[e-mail]:

As far as I know, SQL Server requires you to include the "and column x
is null" *(see my second example) when doing an outer join with the
join conditions in the WHERE clause. *Oracle has a special notation so
you don't have to test for the null condition, but I don't think SQL
2008 does. *Even in Oracle, I don't like that special notation.

When doing any of (relatively) new syntax ON clause is required both
for SQL Server and Oracle, so your last example below is incorrect
(not to speak about some other minor incorrectnesses :). Of course one
can fake both of them using some always true condition for example on
(1=1) but that's quite starnge.

Speaking about special notations for outer joins both SQL Srever and
Oracle had them and have them (for SQL server one has to set lower
compatibility level).
See more about these notations and why there is difference between
join conditions and where conditions in outer joins here
http://www.gplivna.eu/papers/sql_join_types.htm

Gints Plivna
http://www.gplivna.eu