# Thread: best approach "wher in" or JOIN

1. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
INNER join, not LEFT OUTER

3. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322

4. 12 Monkey Method
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
•