Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Difference between EXISTS and a join

    Hi,
    The EXIST predicate is always followed by a subselect.

    For example the get the rows for values in department which have deptno which matches a workdept in the employee table you'd do:

    SELECT deptno, deptname FROM department WHERE EXISTS (SELECT workdept FROM employee WHERE workdept = dept)


    This can also be done by just using a join.

    SELECT department.deptno, department.deptname FROM department, employee WHERE department.deptno = employee.workdept;

    What's the pro's and con's of both approaches?

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can try yourself.

    Second query(using a join) will return multiple rows for a department.
    Exact number of rows for a department will be the number of employees belonging to the department.

    If you want to get same result as first query, you should add DISTINCT keyword for second query.

  3. #3
    Join Date
    Jan 2006
    Posts
    119
    Quote Originally Posted by tonkuma View Post
    You can try yourself.

    Second query(using a join) will return multiple rows for a department.
    Exact number of rows for a department will be the number of employees belonging to the department.

    If you want to get same result as first query, you should add DISTINCT keyword for second query.
    Ok so same question with DISTINCT included?

    Thanks.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to say samethings as Marcus A in this thread
    http://www.dbforums.com/db2/1667964-using-equals.html
    Do an Explain and find out.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    A good rule-of-thumb, in general, is to try putting into subqueries (with EXISTS or IN) all tables from which no direct information (i.e.: column values or counts) are needed.

    This will most often be much more readable that the JOIN, and most often also more performant. Beware: a join could be more performant than an EXISTS. If it really matters: use EXPLAIN to find out, and document the reason for your choice (using "--" comments in SQL).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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