yoshi@nocusuhs.nnmc.navy.mil (Dojun Yoshikami) (04/17/91)
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. (There's gotta be a way to do this! It's a relational algebraic operation which should be easily done!) DY ;-) =-=-=-=-=-=-=-=-=-=-=-=-=-=+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= D M Yoshikami :-) ;-) :-) | Internet: yoshikami@usuhsb.ucc.usuhs.nnmc.navy.mil May the Forth be with you! | Bitnet : yoshikami@usuhsb.bitnet *** !!xob X*NU V METSYS T&TA eht ni renosirp dleh gnieb m`I !pleH !pleH *** =-=-=-=-=-=-=-=-=-=-=-=-=-=+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
cdm@gem-hy.Inel.GOV (Dale Cook) (04/18/91)
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.
yoshi@nocusuhs.nnmc.navy.mil (Dojun Yoshikami) (04/18/91)
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) My heartfelt thanks to all those who replied to my request. I believe I've been a little remiss (my last message didn't have enough information). Anyways, I found to my dismay that the SQL implementation (in this case, INGRES 6.1) seems to lack the MINUS operator for subqueries!! Arrgh!!!!! (No, you don't have to put your flame suit on...!). Anyways, there's probably a workaround somewhere, or something to that effect... or I'll be rather upset! While we're on the subject, did anyone else run into a similar SQL gotcha in INGRES of this kind (or any other kind) that normally appears in SQL but is mysteriously absent? Or is there a way to do the same thing in QUEL? [My boss will kill me if I do anything in Quel, but if there's a will, there's a way!] DY ;-) =-=-=-=-=-=-=-=-=-=-=-=-=-=+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= D M Yoshikami :-) ;-) :-) | Internet: yoshikami@usuhsb.ucc.usuhs.nnmc.navy.mil May the Forth be with you! | Bitnet : yoshikami@usuhsb.bitnet *** !!xob X*NU V METSYS T&TA eht ni renosirp dleh gnieb m`I !pleH !pleH *** =-=-=-=-=-=-=-=-=-=-=-=-=-=+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
jean@beno.CSS.GOV (Jean Anderson) (04/18/91)
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. | +----------------------------------------------------------------------------+
jfr@locus.com (Jon Rosen) (04/18/91)
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 =========================================================