Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!cs.utexas.edu!uunet!pmafire!mica.inel.gov!gem-hy!cdm
From: cdm@gem-hy.Inel.GOV (Dale Cook)
Newsgroups: comp.databases
Subject: Re: SQL Differences Question
Keywords: set differences, SQL
Message-ID: <1991Apr17.203832.5327@inel.gov>
Date: 17 Apr 91 20:38:32 GMT
References: <1991Apr17.133725.25499@nocusuhs.nnmc.navy.mil>
Sender: news@inel.gov
Reply-To: cdm@gem-hy.Inel.GOV (Dale Cook)
Organization: Idaho National Engineering Laboratory, Idaho Falls, Idaho
Lines: 63

In article <1991Apr17.133725.25499@nocusuhs.nnmc.navy.mil>,
yoshi@nocusuhs.nnmc.navy.mil (Dojun Yoshikami) writes:
|> 
|> Question for all you SQL'er's,
|> 
|> How can one get the difference between two sets?  I have two tables
(s, and t)
|> as follows:
|> 
|> S:		T:
|> -----		-----
|> 1		1
|> 2		3
|> 3		7
|> 
|> What I would like to get is all S's that are not in T, i.e. 2 in this
example.
|> I know there is a set algebraic operator (difference) to get this,
but I'm not
|> sure how to get this in SQL. 
|> 

In Oracle, it's the MINUS operator.  Using your example:

SELECT x FROM S
MINUS
SELECT y FROM T

gives the rows in S not in T:

x
----
2

and conversly:

SELECT y FROM T
MINUS
SELECT x FROM S

gives the rows in T not in S:

y
----
7

----------------------------------------------------------------------
--- Dale Cook          "You can sum this game up in one word -
    cdm@inel.gov        'you never know'".  --- J. Andujar
The opinions are mine.  The following disclaimer is my employers.
----------------------------------------------------------------------
========== long legal disclaimer follows, press n to skip ===========

Neither the United States Government or the Idaho National Engineering
Laboratory or any of their employees, makes any warranty, whatsoever,
implied, or assumes any legal liability or responsibility regarding any
information, disclosed, or represents that its use would not infringe
privately owned rights.  No specific reference constitutes or implies
endorsement, recommendation, or favoring by the United States
Government or the Idaho National Engineering Laboratory.  The views and
opinions expressed herein do not necessarily reflect those of the
United States Government or the Idaho National Engineering Laboratory,
and shall not be used for advertising or product endorsement purposes.