how to show differences between sets of data in Oracle

I am learning Oracle Pl/Sql and have a scenario as follows which I can't seems to figure out: I have a legacy table of application settings i.e. see table columns below.


I want to get a list of the settings that exist for Branch 991/Address 123456 that do not exist for 992/Address 987654 i.e rows with ID 5 and 6.

I have a UNION statement that gets me combined but I can't figure out how to get only the differences.


SELECT SYSTEM,KEY1,KEY2,KEY3,KEY4,BRANCH
from SETTING
where ADDRESS='123456' AND BRANCH = '991'
UNION
SELECT SYSTEM,KEY1,KEY2,KEY3,KEY4,BRANCH
from SETTING
where ADDRESS='987654' AND BRANCH = '992' )


I tried adding a WHERE NOT EXISTS to above


WHERE NOT EXISTS
(
select SYSTEM,KEY1,KEY2,KEY3,KEY4,BRANCH
from SETTING
where address='987654' AND BRANCH = '992');


but it returns no records...

This is the data:


ID SYSTEM KEY1 KEY2 KEY3 KEY4 BRANCH ADDRESS
1   ABC   URI  CODE NULL NULL 991    123456
2   ABC   URI  CODE NULL NULL 992    987654
3   ABC   URI  CODE NULL NULL 992    666666
4   ABC   READ REV   1   4    991    123456
5   ABC   READ REV   1   4    992    987654
6   ABC   DEV  OPEN  0   0    991    123456
7   ABC   ICI  ORD   0   0    991    123456


Appreciate any help...

Comments for how to show differences between sets of data in Oracle

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 03, 2016
Rating
starstarstarstarstar

by: John

The best way to find the difference between two sets of data is to use the SQL MINUS clause. This takes one set of data away from another and gives you want's left.

For this problem therefore, the query would be something like this:

SELECT * FROM (
SELECT SYSTEM,KEY1,KEY2,KEY3,KEY4,BRANCH
from SETTING
where ADDRESS='123456' AND BRANCH = '991'
MINUS
SELECT SYSTEM,KEY1,KEY2,KEY3,KEY4,BRANCH
from SETTING
where ADDRESS='987654' AND BRANCH = '992'
)


This SQL statement retrieves the data for address 123456 and branch 981 and then removes form the results any values that are at address 987654 and branch 992. Therefore for this example the result would be:-

SYSTEM,KEY1,KEY2,KEY3,KEY4,BRANCH
ABC READ REV 1 4 991

N.B. The MINUS clause only shows you the row(s) that do not have a match in the other data set - it doesn't show you what the difference is. This is especially important when dealing with numbers. The result of

SELECT 100 FROM DUAL MINUS SELECT 9 FROM DUAL;

is 100 not 91 and similarly the result of

SELECT 100,'ABC' FROM DUAL MINUS SELECT 9,'ABC' FROM DUAL;

is 100,ABC not 100. In other words, any difference between any of the columns in the rows will result in the whole row being identified as being different and returned as part of the result set. However once you've determined which rows are different you can then drill down to see which columns are different by altering the SQL.

See also our series of Oracle SQL tutorials and the Oracle training page for information about formal Oracle training either on-line or in person.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.