Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!helios!bcm!dimacs.rutgers.edu!seismo!beno.CSS.GOV
From: jean@beno.CSS.GOV (Jean Anderson)
Newsgroups: comp.databases
Subject: Re: SQL Differences Question
Keywords: set differences, SQL
Message-ID: <49563@seismo.CSS.GOV>
Date: 17 Apr 91 21:49:16 GMT
Sender: usenet@seismo.CSS.GOV
Organization: SAIC Geophysics Division, San Diego, CA
Lines: 81


In <1991Apr17.133725.25499@nocusuhs.nnmc.navy.mil>, Dojun Yoshikami:
> 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. 

Given:

	SQL> select * from s;      SQL> select * from t;

	S                          T
	---                        ---
	1                          1
	2                          3
	3                          7

These give you the inner and outer joins (if your database does not have 
an outer join operator):

	SQL> select s.s, 'Not in T' t
  	2    from   s
  	3    where  not exists
  	4           (select t from t where s.s=t.t);

	S   T
	--- --------
	2   Not in T

	SQL> select t.t, 'Not in S' s
  	2    from   t
  	3    where  not exists
  	4           (select s from s where s.s=t.t);

	T   S
	--- --------
	7   Not in S

And this will give you the whole kit and kaboodle all together:

	SQL> select s.s, t.t
  	2    from   s, t
  	3    where  s.s=t.t
  	4    union
  	5    select s.s, 'Not in T'
  	6    from   s
  	7    where  not exists
  	8           (select t from t where s.s=t.t)
  	9    union
 	10   select 'Not in S', t.t
 	11   from   t
 	12   where  not exists
 	13*         (select s from s where s.s=t.t);

	S        T
	-------- --------
	1        1
	2        Not in T
	3        3
	Not in S 7


Okey, so I made those numeric columns varchars to make outputting comments
easy. :-)  You could also coerce to char on the output. You get the idea.

+----------------------------------------------------------------------------+
| Jean Anderson, DBA                            email:  jean@seismo.css.gov  |
| SAIC Geophysics Division, Mailstop 12            or:  jean@esosun.css.gov  |
| 10210 Campus Point Drive                      phone:  (619)458-2727        |
| San Diego, CA  92121                            fax:  (619)458-4993        |
+----------------------------------------------------------------------------+
|                Any opinions are mine, not my employer's.                   |
+----------------------------------------------------------------------------+