Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!elroy.jpl.nasa.gov!decwrl!mejac!orchard.la.locus.com!prodnet.la.locus.com!jfr
From: jfr@locus.com (Jon Rosen)
Newsgroups: comp.databases
Subject: Re: SQL Differences Question
Keywords: set differences, SQL
Message-ID: <23596@oolong.la.locus.com>
Date: 18 Apr 91 02:32:12 GMT
References: <1991Apr17.133725.25499@nocusuhs.nnmc.navy.mil>
Organization: Locus Computing Corp, Los Angeles
Lines: 39

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.X            T.X
>-----		-----
>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 DB2 SQL (and in ANSI as well), this can be done albeit kludgily with
a subselect:  
 
       SELECT S.X FROM S WHERE S.X NOT IN (SELECT T.X FROM T)
 
However, this only works for simpleton differences and is not really
a proper implementation of the relational algebraic difference operator.
 
ANSI SQL2 has new syntax for this called EXCEPT:
 
       SELECT S.X EXCEPT SELECT T.X
 
There is also a new OUTER UNION which enables you to do outer join  
operations... ANSI SQL2 has something for everyone (gag -- ;-) 
 
Jon Rosen
=========================================================
"Another birthday?  Well, don't worry about getting old
 until you can't make sense out of the simplest things...
 ... isn't it?" -- from my favorite 40th birthday card
=========================================================