Results 1 to 7 of 7

Thread: Exists vs In

  1. #1
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326

    Unanswered: Exists vs In

    I'm fairly new to Oracle, but have some experience with other RDBMS.

    Can someone explain situations where I would want to use EXISTS instead of IN?

    Thanks.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  2. #2
    Join Date
    Nov 2003
    Location
    MA,USA
    Posts
    18

    Re: Exists vs In

    EXISTS condition tests for existence of rows in a SUBQUERY whereas
    IN operator returns TRUE if its first operand is contained in the set identified by the second operator.

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Right, but in which sort of situations would I use EXISTS where I couldn't use IN? Or is this some optimization technique?

    I can use a subselect with IN as well.

    Sorry, but I'm just a little confused.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Sep 2003
    Posts
    27
    A quick example:

    Take my car insurance.
    The insurers will hike up my premium whenever a claim is made.

    update POLICY
    set PREMIUM = (select PREMIUM * 2)
    where exists
    (select 1 from CLAIMS);

    They don't care what sort of claim it is.
    They don't care that CLAIMS.BLAME = "NOT MY FAULT".
    I don't think they could do this using an "IN" statement.

    Tim

  5. #5
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Thank you, I think I understand it now. It's because (in your example), your insurance company mitigates the base financial risk across all policy holders, they can use EXISTS to do thier calculations in an optimized way rather than creating variable and stored procs.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You missed the correlation:

    update POLICY
    set PREMIUM = (select PREMIUM * 2)
    where exists
    (select 1 from CLAIMS where CLAIMS.policy_id = POLICY.policy_id);

    Without that, the update will double everybody's premiums (or nobody's if nobody has ever claimed).

    And it can be done using IN:

    update POLICY
    set PREMIUM = (select PREMIUM * 2)
    where policy_id in
    ( select policy_id from claims );

    I can't think of a query that can be done one way but not the other (there may be such a query, I just can't think of one!) - other than what we just had, which was the non-correlated exists.

    Generally, a query can be expressed either way, and one way may be more suitable than the other depending on the relative amounts of data. For example:

    select *
    from billion_row_table b
    where exists (select 1 from one_row_table o where o.id = b.id);

    invites the optimizer to full scan a billion rows to find the one row that has a match in the one-row table (2 billion reads), whereas:

    select *
    from billion_row_table b
    where id in (select id from one_row_table)

    tells the optimizer we want to full scan one_row_table (1 read) and then do an indexed lookup in billion_row_table (2-5 reads say)

    Of course, if the optimizer is clever enough and has good enough statistics and metadata, it may be smart enough to use the same best plan either way.

  7. #7
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Ahhh, sweet clarity. That's exactly the explanation I was hoping for.

    Thank you all for your time and input.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •