-
Are these 2 queries same as far as the results are concerned...
SELECT mwebWork.Work_ID, mwebWork.Work_Description,
mwebWork.Work_Plan_Start, mwebWork.Work_Plan_Finish,
mwebWork.Work_Amt_Ext2 FROM mwebWork WHERE Work_ID in (Select distinct Auth_Work_ID From mwebAuth)
AND Work_Par6=16230 AND Work_ID Not IN(SELECT distinct Mat_Work_ID FROM MwebMatrix
where Mat_Category BETWEEN 4000 AND 4999 )
ORDER BY Work_Plan_Start;
SELECT mwebWork.Work_ID, mwebWork.Work_Description,
mwebWork.Work_Plan_Start, mwebWork.Work_Plan_Finish,
mwebWork.Work_Amt_Ext2 FROM mwebWork WHERE Work_ID in (Select distinct Auth_Work_ID From mwebAuth)
AND Work_Par6=16230 AND Not Exists(SELECT count(*) FROM MwebMatrix
where Mat_Category BETWEEN 4000 AND 4999 AND Mat_Work_ID=Work_ID)
ORDER BY Work_Plan_Start;
not in and not exists (.. count(*)...) is where I am confused !!
Which one is better as far as the performance goes ?
Thanks
Sonali
-
Are they logically the same? Yes
The COUNT(*) *should* have you confused. The usual syntax is SELECT 1 - since you are not going to actually do *anything* with the results, it should be visually obvious that they mean nothing. SELECT 1 does that, SELECT COUNT does not
Which is better? Well, that really depends on the situation. Lame answer, I know, but there it is Check the plans and run each version.
Generally, I avoid IN sub-selects (I merge them into the outer query) unless there is a difference of grouping, which yours appears to have. As far as NOT IN vs NOT EXISTS, the NOT IN allows you the option of using the HASH_AJ and MERGE_AJ hints. However, the other thing to consider is that NOT INs can sometimes be re-written as INs, which can, again, be merged into the outer query. Of course, you again have a difference of grouping levels, so that's probably not helpful.
Just for completeness, however, here is yet another way to write the same query...
SELECT
---WW.Work_ID,
---WW.Work_Description,
---WW.Work_Plan_Start,
---WW.Work_Plan_Finish,
---WW.Work_Amt_Ext2
FROM
---mWebWork WW---,
---(
------SELECT DISTINCT
---------Auth_Work_ID
------FROM
---------mWebAuth
---)---------WA---,---
---(
------SELECT DISTINCT
---------Mat_Work_ID
------FROM
---------mWebMatrix---
------WHERE
---------Mat_Category---<---4000---OR
---------Mat_Category--->---4999
---)---------WM
WHERE
---WW.Work_Par6---=---16230---------AND
---WA.Auth_Work_ID=---WW.Work_ID---AND
---WM.Mat_Work_ID---=---WW.Work_ID---
ORDER BY
---WW.Work_Plan_Start;
Hope that wasn't too confusing
- Chris
-
They don't look logically same
...SQLWKS> SELECT mwebWork.Work_ID, mwebWork.Work_Description,
2> mwebWork.Work_Plan_Start, mwebWork.Work_Plan_Finish,
3> mwebWork.Work_Amt_Ext2 FROM mwebWork WHERE Work_ID in (Select distinct Auth_Work_ID From mwebAuth)
4> AND Work_Par6=2649 AND Work_ID Not IN(SELECT distinct Mat_Work_ID FROM MwebMatrix
5> where Mat_Category BETWEEN 4000 AND 4999 )
6> ORDER BY Work_Plan_Start;
WORK_ID WORK_DESCRIPTION WORK_PLAN_START WORK_PLAN_FINISH WORK_AMT_E
---------- -------------------------------------------------------------------------------- -------------------- -------------------- ----------
2651 OI Effort 31-JUL-99 01-JAN-01 0
1 row selected.
SQLWKS>
SQLWKS>
SQLWKS> SELECT mwebWork.Work_ID, mwebWork.Work_Description,
2> mwebWork.Work_Plan_Start, mwebWork.Work_Plan_Finish,
3> mwebWork.Work_Amt_Ext2 FROM mwebWork WHERE Work_ID in (Select distinct Auth_Work_ID From mwebAuth)
4> AND Work_Par6=2649 AND Not Exists(SELECT count(*) FROM MwebMatrix
5> where Mat_Category BETWEEN 4000 AND 4999 AND Mat_Work_ID=Work_ID)
6> ORDER BY Work_Plan_Start;
WORK_ID WORK_DESCRIPTION WORK_PLAN_START WORK_PLAN_FINISH WORK_AMT_E
---------- -------------------------------------------------------------------------------- -------------------- -------------------- ----------
0 rows selected.
SQLWKS>
SQLWKS> SELECT mwebWork.Work_ID, mwebWork.Work_Description,
2> mwebWork.Work_Plan_Start, mwebWork.Work_Plan_Finish,
3> mwebWork.Work_Amt_Ext2 FROM mwebWork WHERE Work_ID in (Select distinct Auth_Work_ID From mwebAuth)
4> AND Work_Par6=2649 AND Not Exists(SELECT 1 FROM MwebMatrix
5> where Mat_Category BETWEEN 4000 AND 4999 AND Mat_Work_ID=Work_ID)
6> ORDER BY Work_Plan_Start;
WORK_ID WORK_DESCRIPTION WORK_PLAN_START WORK_PLAN_FINISH WORK_AMT_E
---------- -------------------------------------------------------------------------------- -------------------- -------------------- ----------
2651 OI Effort 31-JUL-99 01-JAN-01 0
1 row selected.
SQLWKS>
-------------------------------------------
Not IN and select 1 seems to return same number of rows where as the one with the count(*) returns 0 rows... Why ?
I did Explain Plan on these cost wise NOT IN and select 1 are same.
Thanks Chris..
Sonali
Sonali
-
Originally posted by sonaliak
Not IN and select 1 seems to return same number of rows where as the one with the count(*) returns 0 rows... Why ?
Okay, uh, color me perplexed.
Hmmmmmmm
Okay, I just went and tested it. I should've seen this coming...
SELECT COUNT(*) will *always* return a result, but that result might be 0. So an EXISTS ( SELECT COUNT(*), will *always* return TRUE, and a similar NOT EXISTS will always return FALSE.
So, for these to be equivalent, it would have to change to :
SELECT mwebWork.Work_ID, mwebWork.Work_Description,
mwebWork.Work_Plan_Start, mwebWork.Work_Plan_Finish,
mwebWork.Work_Amt_Ext2 FROM mwebWork WHERE Work_ID in (Select distinct Auth_Work_ID From mwebAuth)
AND Work_Par6=16230 AND 0 = (SELECT count(*) FROM MwebMatrix
where Mat_Category BETWEEN 4000 AND 4999 AND Mat_Work_ID=Work_ID)
ORDER BY Work_Plan_Start;
Interesting though.
- Chris
-
Thanks a lot, you are great !
Sonali
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|