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 '12 at 06:34 AM in Default

satya gravatar image

satya
361 18 18 21

(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 '12 at 12:46 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.6k 43 49 76

(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 '12 at 06:44 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x976
x111

asked: Feb 29 '12 at 06:34 AM

Seen: 2244 times

Last Updated: Feb 29 '12 at 02:14 PM