| Author |
Topic |
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
 Posted - 2007-12-16 : 23:28:50
|
| Dear Experts,i've found two types of queries using IN with our functions and prcedures.one type isCASE WHEN A.COL7 IN ('A','F','IBT') THEN.......another type is...where col2 in (select col1 from table33 where col5='code1')i'm changing these two types with exists...first case i'm using a @table and inserting the values in the table and selecting the values from the table.in second case, as you all shown me the way, i'm doing.now my doubt is will the first case really effects the performance? if i'm not changing the in and leaving it as it is.....because in some scenario, the statement like ('A','F','IBT') are with different values. that means i've to use so many @tables.will it worth doing that? why i'm asking like this is ....it is not working on query it is working on predefined data....that's why....i think i've given enough info regarding my doubt...am i right?thanks for the given time to me.....VinodEven you learn 1%, Learn it with 100% confidence. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-16 : 23:44:27
|
I would not change the "IN ('A','F','IBT')" case at all. I'm curious as to what advice led you to believe putting these values in a temp table and using EXISTS would be better?
elsasoft.org |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-16 : 23:45:50
|
| i'm changing these two types with exists...I dont think you can replace an IN with EXISTS in all cases...as IN will look for whether a specified value matches any value in a subquery or a list while EXISTS checks if a subquery contains any rows at all. Even if there is only a single row returned by subquery EXISTS will return true without evaluating whether the column you are comparing contains that returned value. |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-12-16 : 23:46:40
|
| i'm in a belief that EXISTS is better than IN....that's why in case 2 i'm doing like that....after words from you, i understood that there is no need to change the case1.am i correct Jez?thanks a lotVinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-16 : 23:48:07
|
yes that's correct.
elsasoft.org |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-12-16 : 23:49:33
|
quote:
Originally posted by visakh16
i'm changing these two types with exists...I dont think you can replace an IN with EXISTS in all cases...as IN will look for whether a specified value matches any value in a subquery or a list while EXISTS checks if a subquery contains any rows at all. Even if there is only a single row returned by subquery EXISTS will return true without evaluating whether the column you are comparing contains that returned value.
thank you visakh, but even in the inside of exists(subquery) i'm using the where condition to retrieve the particula column.i think it is a good thing.VinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-16 : 23:58:10
|
| That depends on your reqmt. If you are interested to check only if a valid value exists for col1 in table33 for col5='code1' then its ok. But if you are really interested in individual values returned by subquery and want to return results for those cases where col2's values corresponds to returned values of subquery then EXISTS wont be an option. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-17 : 00:06:44
|
imo, blindly applying a rule is not a good idea. sometimes IN is appropriate, sometimes EXISTS, and sometimes neither is appropriate. for instance in your second case you list in your original post, my first instinct would probably be for a join rather than EXISTS.
elsasoft.org |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-12-17 : 00:14:00
|
| You are correct JezVinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
|
regarding IN