execution difference between Inner Join and Where

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

execution difference between Inner Join and Where

Author

Topic

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-10-11 : 10:37:53

Hi all,

This is the situation, I need to write a simple query joining 4 tables with some criteria. Actually one of the four tables is having the criteria stored in it.

Now, what i did was, I wrote the select statment with the inner joins on only the primary and foreign keys and specified the rest of the criteria in the where clause.

If I have to rewrite the statement to specify the criteria in the inner joins of the table and take out where clause altogether, how different would this be to the previous statement with where clause in terms of execution. Please let me know the difference.

Thanks a lot in advance,
Nitu

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-11 : 10:42:16

The query plan should be the same but why not try it and see.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-11 : 10:43:11

There is a small gain in writing the WHERE statements directly in the JOINs.
Post here what you have so far.

Peter Larsson
Helsingborg, Sweden

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-10-11 : 10:49:32

Thanks for the reply nr,

I tried both of them, both took exactly the same amount of time for execution. I just wanted to know if using a where clause would make a lot of difference when compared to the inner join.

--Nitu

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-10-11 : 11:01:50

Thanks for the reply Peso,

Here is the example,

Table1: t1f1, t1f2, t1f3, t1f4, t1f5, t1f6, t1f7, t1f8, t1f9, t1f10
Table2: t1f1, t2f1, t2f2, t2f3, t2f4, t2f5, t2f6, t2f7
Table3: t2f1, t3f1, t3f2, t3f3, t3f4, t3f5, t3f6, t3f7, t3f8, t3f9, t3f10
Table4: t3f1, t4f1, t4f2, t4f3, t4f4, t4f5, t4f6, t4f7

select t1f2, t1f3, t1f4, t1f5, t1f6, t2f2, t2f3, t3f2, t3f3, t3f4, t3f5, t4f2, t4f3, t4f4, t4f5, t4f6
from table1
inner join on table1.t1f1 = table2.t1f1
inner join on table2.t2f1 = table3.t2f1
inner join on table3.t3f1 = table4.t3f1
where t1f2 = t2f2
and t1f7 = t4f7
and t2f6 = t4f6
and t2f2 = t3f3
and t3f4 = t4f4
and t3f5 = t2f5

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-10-11 : 12:08:13

Sorry for the previous example, Here is a better explained example.

There are 3 tables, CriteriaTable(ct), ItemTable(it), RateTable(rt)

ct: product (a mandatory field for user), element (mandatory), sub (optional), subsub (optional), charge (optional), speedunits (mandatory), speed (mandatory), itemtype(mandatory), ratecategory (optional)

Imp Note: When user doesnt enter the optional fields, its stored as a null value. Which is then handled in the query. It is supposed to get all the available records for such fields.

it: itemno, product, element, sub, subsub, charge, speedunits, speed, itemtype

rt: itemno, ratecat, yr1, yr2, yr3, yr4, yr5, yr6, yr7, yr8, yr9, yr10, notes.

Resulting report should contain the fields:
itemno, Product, element, sub, subsub, charge, speedunits, speed, itemtype, yr1, yr2, yr3, yr4, yr5, yr6, yr7, yr8, yr9, yr10, notes.

Query1:
Select itemno, product, element, sub, subsub, charge, speedunits, speed, itemtype, yr1, yr2, yr3, yr4, yr5, yr6, yr7, yr8, yr9, yr10, notes
From it
Inner Join ct on ct.product = it.product
Inner Join rt on it.itemno = rt.itemno
Where ct.element = it.element
and isnull(ct.sub, '-1') =
Case when isnull(it.sub, '-1') = '-1' Then '-1'
Else it.sub
End
and isnull(ct.subsub, '-1') =
Case when isnull(it.subsub, '-1') = '-1' Then '-1'
Else it.subsub
End
and isnull(ct.charge, '-1') =
Case when isnull(it.charge, '-1') = '-1' Then '-1'
Else it.charge
End
and ct.speedunits = it.speedunits
and ct.speed = it.speed
and ct.itemtype = it.itemtype
and isnull(ct.ratecategory, '-1') =
Case when isnull(rt.ratecat, '-1') = '-1' Then '-1'
Else rt.ratecat
End
Order by it.product, it.itemno, it.element

Query2:
Select itemno, product, element, sub, subsub, charge, speedunits, speed, itemtype, yr1, yr2, yr3, yr4, yr5, yr6, yr7, yr8, yr9, yr10, notes
From it
Inner Join ct on ct.product = it.product
and isnull(ct.sub, '-1') =
Case when isnull(it.sub, '-1') = '-1' Then '-1'
Else it.sub
End
and isnull(ct.subsub, '-1') =
Case when isnull(it.subsub, '-1') = '-1' Then '-1'
Else it.subsub
End
and isnull(ct.charge, '-1') =
Case when isnull(it.charge, '-1') = '-1' Then '-1'
Else it.charge
End
and ct.speedunits = it.speedunits
and ct.speed = it.speed
and ct.itemtype = it.itemtype
Inner Join rt on it.itemno = rt.itemno
and isnull(ct.ratecategory, '-1') =
Case when isnull(rt.ratecat, '-1') = '-1' Then '-1'
Else rt.ratecat
End
Order by it.product, it.itemno, it.element

Among the two queries, Query1 & Query2, What are all the differences in terms of execution. How are the two parsed.
When I ran the two queries, I got the same result, and the time of execution was exactly same. Please explain the difference between the two approaches if there is any.

Thanks a lot in advance,
Nitu

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-10-11 : 14:31:39

Appretiate if somebody could help me with this.

Thanks,
Nitu

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-10-11 : 16:41:35

Hi Mr. Peter, Cant you please help me out on this one.

Pretty Please.

It has been bugging me for so many days. Even though I am past that situation now, I just cant rest until I get an answer.

Thanks a lot in advance,
Nitu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 01:36:25

Something like this?

SELECT		t1.t1f2,
		t1.t1f3,
		t1.t1f4,
		t1.t1f5,
		t1.t1f6,
		t2.t2f2,
		t2.t2f3,
		t3.t3f2,
		t3.t3f3,
		t3.t3f4,
		t3.t3f5,
		t4.t4f2,
		t4.t4f3,
		t4.t4f4,
		t4.t4f5,
		t4.t4f6
FROM		table1 t1
INNER JOIN	table2 t2 ON t2.t2f1 = t1.t1f1 AND t2.t2f2 = t1.t1f2
INNER JOIN	table3 t3 ON t3.t3f1 = t2.t2f1 AND t3.t3f5 = t2.t2f5 AND t3.t3f3 = t2.t2f2
INNER JOIN	table4 t4 ON t4.t4f1 = t3.t3f1 AND t4.t4f4 = t3.t3f4 AND t4.t4f6 = t2.t2f6 AND t4.t4f7 = t1.t1f7

Peter Larsson
Helsingborg, Sweden

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-10-12 : 13:50:04

Thanks for replying Mr Peter.

I think I dint present my question that well. I know that the query can be written with all the criteria in the inner joins, and that it can also be written with the criteria in the where clause.

Interms of performance, the way the SQL Server parses the query, what is the differece between the two approaches.

Can you please help me with this.

Really appretiate your help,
Thanks in advance,
Nitu

stephe40
Posting Yak Master

218 Posts

Posted - 2006-10-12 : 14:56:45

Both are going to probably perform the same because both go through the query optimizer, which is smart enough to put the join conditions in place where it yields the best performance. At this point its probably a matter of preference. I would put the expressions as part of the join clause, and not in the where clause, like Peso's example above. I typically only put expressions that involve a variable or constant in the where clause.

- Eric

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-12 : 14:59:28

"I tried both of them, both took exactly the same amount of time for execution."

If you already know the answer to your question, why are you begging Peso for an answer?

Have you looked at the execution plans of both versions of your query?

Jay White

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-10-12 : 15:52:45

I am not sure though, but i think i compared the execution plans for both of them and couldnt find much of a difference. Honestly I am not sure of this.

The reason I have been seeking an answer for my question

quote:

Interms of performance, the way the SQL Server parses the query, what is the differece between the two approaches.

is because, I was criticised (or to say honestly been almost yelled yet) so badly that somebody went as far as asking me if i was in my senses to write such a kind of a query (when i used the where clause - coz even though its in a table, since its basically a set of criteria), which really pissed me off. But then again I want to know if I really made such a big mistake by using the where clause, so that I can correct myself for future purposes.

--Nitu

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-12 : 16:09:29

I was more disturbed by the fact that you wrote:

quote:

Where ct.element = it.element
and isnull(ct.sub, '-1') =
Case when isnull(it.sub, '-1') = '-1' Then '-1'
Else it.sub

instead of just:

Where ct.element = it.element
and isnull(ct.sub,'-1') = isnull(it.sub,'-1')

And why do you have the numbers as strings?
And I would yell at the database designer for allowing nulls!

The writing of the query with join vs where syntax is imo no grounds for yelling.
Even if the join syntax imo is better in tis case.

My rules of thumb:
In the JOIN clause - write the join criteria (how the sets are joined, loosely speaking)
In the WHERE clause - write the filtering criteria (how the resulting set is to be filtered)

rockmoose

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-10-12 : 17:16:11

I am extremely sorry, the logic was something like this,

Where ct.element = it.element
and isnull(ct.subsub, '-1') like
Case when isnull(ct.subsub, '-1') = '-1' Then '%'
Else isnull(it.subsub, '-1')

What i did was if the user choses to see all the subsub (an optional field for user) values from the DB, then he can choose not to provide a criteria for subsub. some mandatory fields like speed, it has null values in the table though (I did not design the database, person who yelled designed it). Since there are null values in the table, if user selects nothing, and there are null values in the table, then he should see the null value record and the rest of the records with values also.

I used string in isnull, since i wanted to do like on '%' to get all the values.

--Nitu

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-12 : 17:41:22

Nitu, whenever someone calls my code crap, I usually ask them to show me how to make it better and show me why it is better, rather than ask a bunch of stranger on some website. What are you going to do, go back to you challenger and say "Well, the people at SQLTeam.com said I was right"? That won't get you anywhere.

If you guys need help analyzing query plans or determining the best way to time queryies, let us know ...

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources