473,327 Members | 2,118 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

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é
Oct 31 '08 #1
12 25047
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
Oct 31 '08 #2
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
Nov 1 '08 #3
>>. 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.
Nov 2 '08 #4
--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.
Nov 3 '08 #5
>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.
Nov 3 '08 #6
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

Nov 4 '08 #7
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

Nov 4 '08 #8
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 <esq...@sommarskog.sewrote:
bill (billmacle...@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, esq...@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
Nov 6 '08 #9
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

Nov 6 '08 #10
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)
Nov 7 '08 #11
On 7 Nov., 23:09, DA Morgan <damor...@psoug.orgwrote:
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
Nov 8 '08 #12
On 4 Nov., 09:21, bill <billmacle...@gmail.comwrote:
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
Nov 8 '08 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: B. Fongo | last post by:
------=_NextPart_000_0007_01C36311.DEE8D2E0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello! I ' m trying to extra some information from 2 tables using...
3
by: Ike | last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL 4.0.20-standard. Thus, in trying to expedite the query, I am trying to set indexes in my tables. My query requires four...
4
by: DCM Fan | last post by:
{CREATE TABLEs and INSERTs follow...} Gents, I have a main table that is in ONE-MANY with many other tables. For example, if the main table is named A, there are these realtionships: A-->B...
23
by: Brian | last post by:
Hello All - I am wondering if anyone has any thoughts on which is better from a performance perspective: a nested Select statement or an Inner Join. For example, I could do either of the...
26
by: Jeff | last post by:
Ok gang. Here is something complicated, well, at least to me anyway. Using Access DB I have a table in my DB called members. In that table, I have 2 tables I will be using "username" and...
6
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: ...
9
by: Emin | last post by:
Dear Experts, I have a fairly simple query in which adding a where clause slows things down by at least a factor of 100. The following is the slow version of the query ...
6
by: jackal_on_work | last post by:
Hi Faculties, I have two queries which give me the same output. -- Query 1 SELECT prod.name, cat.name FROM products prod INNER JOIN categories cat ON prod.category_id = cat.id WHERE cat.id...
2
by: ephraem | last post by:
Im having a problem with my code using where code on the inner join Data5.recordsource = "select S.Cpartycode, S.rate, S.valdate, S.Matdate, S.Tenor, D.ID, D.Counterparty from S where rate > 3000...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.