Query help

[sonaliak]

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

[chrisrlong]

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

[sonaliak]

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

[chrisrlong]

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

[sonaliak]

Thanks a lot, you are great !