2

I have some doubts on when to use IN operator and Exists operator. I know what they both are.

Say, I have this query: 

Insert into AuditCardTypeBenefit_New(AuditID, AuditType, CardTypeBenefitID, EventCode,
                                     CardTypeGroupID, AgeFrom, AgeTo, Gender,
                                     CreateBy, CreateDate,  Status,
                                     CancelReason, LastChangeBy, LastChangeDate) 
     select                 
         AuditID, AuditType, CardTypeBenefitID, EventCode,
         CardTypeGroupID, AgeFrom, AgeTo, Gender,
         CreateBy, CreateDate, Status,
         CancelReason, LastChangeBy, LastChangeDate
     from 
         AuditCardTypeBenefit
     where 
         exists (select * from PromotionEvent_New 
                 where PromotionEvent_New.EventCode = AuditCardTypeBenefit.EventCode)

I am inserting records to a table where the records must also exist in promotionevent_new (eventcode) to exist in this table I am inserting. This query works fine. I was wondering would it be better to use an alternative such as:

where EventCode in (select EVENTCODE from PromotionEvent)

Both queries return the same result - but which would be better for my case?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cookie Monster
  • 636
  • 1
  • 12
  • 29

2 Answers2

2

Would it be better? We cannot really answer that definitely as you really need to do testing. However I am willing to say it is unlikely you will get faster results by using IN() in that query.

An EXISTS subquery is unusual because it uses a "correlation" and we can often consider these to be things to avoid. However testing if something "exists" does NOT have to return any stored data values, it is similar to a join in this respect and all that is necessary is that true or false is returned. On the other hand IN does require data to be returned by the subquery and values are compared.

In recent years the performance of IN() has been improved so it can be very effective, but in performance terms there isn't sufficient inventive to change your existing query.

NB: NOT IN(subquery-here) suffers if any values returned by a subquery are NULL.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • `NB: IN(subquery-here) suffers if any values returned by a subquery are NULL`: `NOT IN` suffers, if there is `NULL` value. `IN` should behave like `EXISTS`, it means, without `IS NULL` clause will return the same values. – Rokuto Nov 21 '17 at 07:26
1

The EXISTS clause is much faster than IN when the subquery results are very large. Conversely, the IN clause is faster than EXISTS when the subquery results are very small.

The IN clause can't compare anything with NULL values, but the EXISTS clause can compare everything with NULLs.

Nandish B
  • 212
  • 1
  • 12