Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    11

    Unanswered: best approach "wher in" or JOIN

    Hi,

    I have 2 sets
    Set1 has few UNIQUE items say workitem Ids 2,3,4

    Set2 has multiple items say workitems Ids is 1,2,2,4,3,5,4,3,6,7,8

    I need to look for Set1 in Set2 so i get 2,3,4 etc...

    which is the best way

    Set2 Left join Set1

    OR

    Select item from Set2 where item in (Set1)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    INNER join, not LEFT OUTER
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I posted your answer over at SQLTeam
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    see if this helps

    books online {Using EXISTS and NOT EXISTS to Find Intersection and Difference} QUOTED IN ENTIRETY

    Subqueries introduced with EXISTS and NOT EXISTS can be used for two set-theory operations: intersection and difference. The intersection of two sets contains all elements that belong to both of the original sets. The difference contains elements that belong only to the first of the two sets.

    The intersection of authors and publishers over the city column is the set of cities in which both an author and a publisher are located.

    USE pubs
    SELECT DISTINCT city
    FROM authors
    WHERE EXISTS
    (SELECT *
    FROM publishers
    WHERE authors.city = publishers.city)

    Here is the result set:

    city
    --------
    Berkeley

    (1 row(s) affected)

    Of course, this query could be written as a simple join.

    USE pubs
    SELECT DISTINCT authors.city
    FROM authors INNER JOIN publishers
    ON authors.city = publishers.city

    The difference between authors and publishers over the city column is the set of cities where an author lives but no publisher is located, that is, all the cities except Berkeley.

    USE pubs
    SELECT DISTINCT city
    FROM authors
    WHERE NOT EXISTS
    (SELECT *
    FROM publishers
    WHERE authors.city = publishers.city)

    This query could also be written as:

    USE pubs
    SELECT DISTINCT city
    FROM authors
    WHERE city NOT IN
    (SELECT city
    FROM publishers)
    Last edited by Ruprect; 12-15-04 at 18:09.

Posting Permissions

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