x

which one runs first in query execution WHERE CLAUSE orJOIN CLAUSE ?

Hi,

Any one please clarify my doubt,

In one of the query I am retrieving data by joining the 5 tables but in that one of the table contains 4 millions records are soft deleted ( Is_deleted_ind = 'Y'), we should not consider the soft deleted records.

So which one gives better performance to write a query

 table1 Inner join table2 on table1.case_id = table2.case_id and Is_deleted_ind = 'N'

OR

 table1 Inner join table2 on table1.case_id = table2.case_id where Is_deleted_ind = 'N'


and which one runs first where clause or join clause while running the query.

Thanks in Advance.

more ▼

asked Feb 29, 2012 at 06:34 AM in Default

avatar image

satya
361 18 19 26

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

2 answers: sort voted first

Whilst the query is an inner join, then yes there is no difference.

If the query was to be changed to an outer join, then it's a different story.

Lets show an example

 declare @table1 table (id int, fruit varchar(50))
 insert into @table1 select 1, 'apple'
 insert into @table1 select 2, 'banana'
 insert into @table1 select 3, 'pear'
 insert into @table1 select 4, 'peach'
 
 declare @table2 table 
   (id int, fruitid int, colour varchar(50), is_deleted_ind char(1))
 insert into @table2 select 1, 1, 'red', 'N'
 insert into @table2 select 1, 1, 'green','N'
 insert into @table2 select 1, 2, 'yellow','N'
 insert into @table2 select 1, 3, 'green','N'
 insert into @table2 select 1, 4, 'red','N'
 insert into @table2 select 1, 1, 'blue','Y'
 insert into @table2 select 1, 5, 'red', 'N'

So we can see that these 2 queries return the same 5 rows

 select *
 from @table1 table1
 inner join @table2 table2 on table1.id = table2.fruitid 
         and table2.is_deleted_ind = 'N'
 
 select *
 from @table1 table1
 inner join @table2 table2 on table1.id = table2.fruitid 
 where table2.is_deleted_ind = 'N'

but changing to a outer join (where the is_deleted_ind is on the side of the join that we want all the rows from)

 select *
 from @table1 table1
 right join @table2 table2 on table1.id = table2.fruitid 
           and table2.is_deleted_ind = 'N'
 
 select *
 from @table1 table1
 right join @table2 table2 on table1.id = table2.fruitid 
 where table2.is_deleted_ind = 'N'

The first query returns 7 rows as the is_deleted_in is part of the JOIN condition

 id          fruit    id          fruitid     colour     is_deleted_ind
 ----------- -------- ----------- ----------- ---------- --------------
 1           apple    1           1           red        N
 1           apple    1           1           green      N
 2           banana   1           2           yellow     N
 3           pear     1           3           green      N
 4           peach    1           4           red        N
 NULL        NULL     1           1           blue       Y
 NULL        NULL     1           5           red        N
 
 (7 row(s) affected)

but the second query returns 6 rows as the is_deleted_ind is a filter on the result of the join, rather than as part of the join

 id          fruit    id          fruitid     colour     is_deleted_ind
 ----------- -------- ----------- ----------- ---------- --------------
 1           apple    1           1           red        N
 1           apple    1           1           green      N
 2           banana   1           2           yellow     N
 3           pear     1           3           green      N
 4           peach    1           4           red        N
 NULL        NULL     1           5           red        N
 
 (6 row(s) affected)


So what's the point of all this....

Code the query to be right. If is_deleted_ind is part of the join condition, then put it there, otherwise if it is a filter then put it in the where clause. This will prevent a whole heap of headaches if someone comes along in the future and simply changes an inner join to an outer join.

more ▼

answered Feb 29, 2012 at 12:46 PM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

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

In this case, short answer is It will not matter. They would have the same execution plan and same throughput. The optimizer is smart enough to handle them as the same :) But I will prefer the WHERE clause for better readability.

more ▼

answered Feb 29, 2012 at 06:44 AM

avatar image

Usman Butt
13.9k 6 12 21

(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:

x1066
x148

asked: Feb 29, 2012 at 06:34 AM

Seen: 4981 times

Last Updated: Feb 29, 2012 at 02:14 PM

Copyright 2016 Redgate Software. Privacy Policy