Query help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Query help

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  5. #5
    Join Date
    Jan 2001
    Posts
    318
    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
  •  



Click Here to Expand Forum to Full Width