BorlandTalk.com

Alan · Guest

UNION is the keyword to combine 2 dataset in SQL, what is the keyword to do
the opposite ?

I want to find out the data that exists in one table but not in the other,
for example,
Table 1: 1, John
2, Peter

Table 2: 1, Sam
2, Peter

I want to get the result is 1, John, ie, record in Table 1 but not in Table
2.
Note: I am using MySQL 4.0, it does not support subquery.

Arno Brinkman · Guest

Hi,

Daryl · Guest

Hi Alan
The word you are looking for is probably INTERSECTION. However it is not a
keyword with SQL server anyway.

From SQL Server on-line books.

Hope it helps...

Using EXISTS and NOT EXISTS to Find Intersection and Difference
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)
[snip]

Ray Marron · Guest

"Alan" <NOSPAMalan_pltse (AT) yahoo (DOT) com.au> wrote

Alan · Guest

Yes, it works.
Thank you.

corey lawson · Guest

"Alan" <NOSPAMalan_pltse (AT) yahoo (DOT) com.au> wrote in
news:4111cb5f$1 (AT) newsgroups (DOT) borland.com: