IN and Exists in SQL?

220 pts.
Tags:
Oracle 10g Express
Oracle SQL
SQL
SQL commands
SQL Server
What is the difference between IN and EXISTS IN SQL?
I googled but I dint get good information. PLease provide me the exact deifference between them.


Software/Hardware used:
Oracle 10 g express edition
ASKED: November 24, 2010  7:18 AM
UPDATED: November 26, 2010  10:11 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is difference in between them :
When coding a SQL statement with tables in master-detail relationships, it’s common to have to decide whether to write the query using the WHERE EXISTS clause or the WHERE value IN clause. U may resist using WHERE EXISTS because it has the awkward syntax of returning a value, which may u always ignore.
When u write a query using the IN clause, the rule-based optimizer that u want the inner query to drive the outer query (let IN = inside to outside). For a general purpose take an e.g., to query the salary table for the direct reports to the employee JOHN, u could write the following:
select ename from salary s
where mgnr in (select empno from salary where ename = ‘JOHN’);

U can write the same query using EXISTS by moving the outer query column to a sub query condition, like this:
select ename from salary s
where exists (select 0 from salary where e.mgnr = empno and ename = ‘JOHN’);
(check for syntax)
When u write EXISTS in a where clause, the optimizer that u want the outer query to be run first, using each value to fetch a value from the inner query (Let EXISTS = outside to inside).
To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In my point of view, EXISTS is better because it requires to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the sub query are very small. Usually to run the query that returns the smaller set of results first.

Is this info helps u !

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    As Rechil explained, the main difference would be performance. If the conditions are correct the results would be the same, but the queries would be executed in a different way by the database engine. When the appropriate index exists, the EXISTS approach usually performs better. You could use the autotrace option in SQL*Plus or SQL Command Line to see the execution plan for your queries, or could use some other tool such as SQL Developer or Toad. To turn autotrace on, use the following command:
    SET AUTOTRACE ON;
    Feel free to ask for clarification if needed.
    65,110 pointsBadges:
    report
  • Kccrosser
    A simpler explanation may help... The IN clause is generally more resource-intensive, because the subquery is executed and returns ALL the records matching the criteria. The entire result set of the subquery is then matched to the outer condition, yielding a "true" condition if one or more of the results satisfies the criteria. Thus, the IN clause can be thought of as similar to a natural JOIN against a temporary table consisting of the IN subquery result set (except that a natural JOIN could result in a Cartesian product expansion of the results, while IN will always only produce one result). The EXISTS clause returns a "true" condition if one or more records in the subquery matches the outer condition. This allows the subquery to terminate as soon as the first matching record is found. The IN clause is best used when you know, a priori, that the subquery will return very few records. When the number of potential records is unknown, or known to be possibly large, then the EXISTS clause is usually much more efficient. Consider the following query - find all people named SMITH that own a Toyota. Here are three possible queries (assumes a simple structure - PEOPLE table with PeopleID, LastName, etc., plus a VEHICLE table with OwnerID, Make):
    select distinct p.*
    from 
        PEOPLE p,
           join VEHICLE v on v.OwnerID = p.PeopleID
               and v.Make = 'TOYOTA'
    where
        p.LastName = 'SMITH'
    Using IN:
    select p.*
    from PEOPLE p
    where p.LastName = 'SMITH'
       and p.PeopleID in (select v.OwnerID from VEHICLE v
           where v.OwnerID = p.PeopleID
                 and v.Make = 'TOYOTA')
    Using EXISTS:
    select p.*
    from PEOPLE p
    where p.LastName = 'SMITH'
       and exists (Select 1 from VEHICLE v
           where v.OwnerID = p.PeopleID
               and v.Make = 'TOYOTA')
    Note that the natural JOIN approach needs to use DISTINCT to discard duplicate records, in case people named SMITH own more than one Toyota. If there were 1000 people named SMITH, and on average they owned 1.5 Toyotas, this query would generate 1500 result rows which would then have to be filtered to remove duplicates by the DISTINCT clause. The IN clause approach will return result sets containing ALL red Toyotas owned be each person named Smith. In this particular query, that isn't likely to be a serious performance problem, but it can be an issue with subqueries returning larger sets. Again, the total retrieved rows in the subquery would be 1500, but using the IN clause would cause the duplicate returns to be ignored. This is more efficient than the natural JOIN, as only 1000 PEOPLE records need to be retrieved, and there is no need to sort/filter those results. The EXISTS clause approach would only retrieve 1000 rows in the subquery, since the subquery would stop as soon as the first record was found that satistied the condition. So, in this situation, the EXISTS clause would require 2/3 the resources of the IN clause, and even less than that compared to the JOIN. NOTE - both IN and EXISTS should normally be used as "dependent" subqueries - i.e., the subquery should contain a reference to some external query condition to minimize the subquery result set. There are exceptions, but only when the subquery is known to return a very small result set without the additional criteria.
    3,830 pointsBadges:
    report
  • TomLiotta
    What is the difference between IN and EXISTS IN SQL? The question asks about SQL, but it's tagged with "Oracle 10g". For some good backround in Oracle, you might study this discussion -- Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT EXISTS, IN, and NOT IN . Lots of good Oracle info there. A very specific difference shows up when NOT IN and NOT EXISTS are compared. A null in the returned rows can cause NOT IN to evaluate FALSE, while nulls generally won't affect the NOT EXISTS result because it is only concerned with whether or not any row satisfying the condition is returned. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following