0

DB - Oracle and MSSQL

How do I get A difference B ? A is what I need - but if a similar record exists in B , don't show it . Similar here means 2 columns are same . [This makes it unique btw] All that I know about A and B is that there is a 2 column combination is UNIQUE record . Say Country and City in the below example . It might have entirely different columns but one record per Country and City on which I need to do my diffence operation .

I have 2 tables , one that says here is the country and the city I am interested in .

A

Country City       Population Dentiy
USA     NewYork    10 mil
USA     Nevada     1  mil
Usa     Penn       3  mil

Another table that says "I have a list of surveyed Cities by some of our guys maybe if you have the data in this table you don't do anything , we will get it for you" .

B

Usa   NewYork
SA    Capetown

I want A difference B , i.e things that are there in A but not there B . And A might have other columns too ... If a Country City in A is there in B , I don't want that record . How do I achieve this ?

PS : The table A and B might be dynamically computer one's ! Using subquery , so I might use with A as and with B as ...

*A is a dynamically generated table , same with B * so I need to use suitable aliasings . The answer should assume this pre-condition .

Nishant
  • 20,354
  • 18
  • 69
  • 101
  • 1
    You probably want to read this: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join – Radu C Jul 03 '13 at 19:47
  • INNER JOIN and OUTERJOIN is not my scenario I think . I am looking for a set operation ? – Nishant Jul 03 '13 at 19:49
  • 1
    Like `SELECT A.* FROM A LEFT JOIN B ON A.Country = B.Country AND A.City = B.City WHERE B.Country IS NULL AND B.City IS NULL`? – Radu C Jul 03 '13 at 19:52
  • 1
    Have a look at this answer to the question I mentioned: http://stackoverflow.com/a/16598900/380140 ; it describes joins as set operations. – Radu C Jul 03 '13 at 19:54
  • INNER and OUTER JOIN will work as set operations. Oracle for instance also supports MINUS and INTERSECT operators. – Randy Jul 03 '13 at 19:55
  • Radu the query seems to suit my condition yes . Let me check further . Basically looking for an extrapolation of http://timsinajaya.wordpress.com/2010/09/30/set-difference-in-sql-m-sql-server/ , difference is that , here 2 things make it unique . – Nishant Jul 03 '13 at 19:56
  • Nishant: If you set your indexes properly (index on (Country,City)?), that join condition I wrote (which includes the two things that make the records unique) should work OK. – Radu C Jul 03 '13 at 19:58
  • But A and B is dynamic , so I have to do something like 'with A as (subquery) B as (subquery)' and then your condition ? A and B have to be computed . Otherwise your query seems good yes. Also I wanted to know if there are native operators that do this . The big problem is we have a flaw in the App logic and we are derviging things from DB . I can do this from Programme but looking for a QUERY that does the above. Yes Index will hold btw . – Nishant Jul 03 '13 at 19:59
  • Well... I'm not sure how subqueries behave in this case. I assume you'd en up using the indexes of the source tables [experimentation needed]. I'm a MySQL guy, so my knowledge is limited to what I've just said. I'd use the `EXPLAIN` keyword to unserstand the interactions. – Radu C Jul 03 '13 at 20:04
  • Another way I'd write my query above would be: `SELECT A.* FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.Country = B.Country AND A.City = B.City)`; I have no idea if this ports past MySQL though. Writing it here just in case it's helpful. – Radu C Jul 03 '13 at 20:08
  • 1
    I've just run a test with `SELECT A.* FROM (SELECT stuff) A LEFT JOIN (SELECT otherstuff) B ON A.Country = B.Country AND A.City = B.City WHERE B.Country IS NULL AND B.City IS NULL` and while `EXPLAIN` looks really strange, it appears to be working. I'm not posting an answer because of your requirement to know if there are any native keywords that give you this result, and I don't know the answer to that one :) But this Anon guy appears to be really happy to copy/paste our discussion into his own answer, including stuff from your link as well :) – Radu C Jul 03 '13 at 20:20
  • Ok I would appreciate if you can just paste it as an answer so I can give you a Correct Answer since it solved my problem :) I was just looking for that , native things apart , I wanted to know what to do really - somehow A.Country = B.Country AND A.City = B.City WHERE B.Country IS NULL AND B.City didn't come to me , the 2 index based join thing . Its simple and hey - why do we want native if things are simple as that ! – Nishant Jul 06 '13 at 18:46

1 Answers1

1

A minus B:

SELECT 
  a.*
FROM a
LEFT OUTER JOIN b ON (a.key = b.key)
WHERE b.key IS NULL

or (MSSQL):

SELECT col1,col2,col3 FROM a
EXCEPT
SELECT col1,col2,col3 FROM b
Anon
  • 10,660
  • 1
  • 29
  • 31