[AccessD] Using Where Exists in SQL - Hello Susan

Geez.... Thank you. ;) That's my granddaughter Lexie when we were both much
younger. She's in school now and I'm an old wrinkled hag. :) 

Some articles are written with just the technique in mind -- and there's
really not much evaluation within the context of the larger picture.
Meaning... I seldom spend a lot of time on "you shouldn't use this when..."
unless it's specific to the technique or it's a big gotcha. 

Personally, I like EXISTS and IN, but I often find they are totally
unnecessary. I'm not convinced either makes things faster, but often, I find
I can write the solution faster using them -- FWIW. 
 
Susan H. 

Hello List,

As some of you may have seen, I am currently working on a project that is
using Access 97 and Oracle 8i.  Having seen a lot of use of the Where Exists
predicate in here, I thought I should try to get my head around it, so, I
went on to the trusty web and guess who's article I came across, our very
own Ms Harkins.

So, to take my question back to our list, which of the following two SQL
statements would you guys use.  Basically, I am trying to see the benefit or
cost of using Where Exists

Thanks, hope you find it interesting and if you have any comments to make
fire them back, if not, no problem, do not worry about it, this is not
holding me up.

Susan, thanks for your contribution to the world and subsequently, to me.
BTW, the two ladies in the photo are really pretty !


Notes


ED_RCPT_TMP_DTLS contains 10,000 records HSET_TACS contains 720 Records

Option 1

Update chs.ED_RCPT_TMP_DTLS
    Set (MODEL_CODE) = 
        Select MODEL_CODE
        From HSET_TACS
        Where
        )
Where Exists (
                Select * from HSET_TACS, ED_RCPT_TMP_DTLS
                Where HSET_TACS.TAC_CODE = ED_RCPT_TMP_DTLS.TAC_CODE
             )



Option 2

Update chs.ED_RCPT_TMP_DTLS
    Set (MODEL_CODE) = 
        (
        Select chs.MODEL_CODE
        From HSET_TACS
        Where HSET_TACS.TAC_CODE = ED_RCPT_TMP_DTLS.TAC_CODE
        )
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com