Home » SQL & PL/SQL » SQL & PL/SQL » Minus Question
Minus Question [message #198834] Wed, 18 October 2006 14:50 Go to next message
Shain Miley
Messages: 6
Registered: July 2004
Junior Member
Hello,
I have been using minus in my sql statements for the last few days to try to uncover areas in which my two database tables are inconsistent. It has been working fairly well, however I was hoping that someone could help me with an issue that I am having that I cannot seem to resolve.

Here is what I am doing:

select id,name from l_table
minus
select id,name from m_table


Here is what I want to do:

What I would like to do is to be able to see not only the results that are missing...but what is there in the other table if for example the names do match up but the id's don't or the id's do but the names don't.

I hope this makes sense....thanks in advance.

Shain
Re: Minus Question [message #198842 is a reply to message #198834] Wed, 18 October 2006 16:46 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
-- ids match but different name

select id, name from l_table l
 where exists (select null
                 from m_table m
                where m.id = l.id
                  and m.name <> m.name);

-- names match but different ids

select id, name from l_table l
 where exists (select null
                 from m_table m
                where m.name = m.name
                  and m.id <> m.id);


And variations on this theme...
Re: Minus Question [message #198846 is a reply to message #198842] Wed, 18 October 2006 17:27 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2151582681236
Re: Minus Question [message #198867 is a reply to message #198846] Wed, 18 October 2006 21:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It was only mentioned in passing in Andrew's link, but I would recommend FULL OUTER JOIN.

Ross Leishman
Re: Minus Question [message #199138 is a reply to message #198834] Fri, 20 October 2006 08:51 Go to previous message
yogen
Messages: 39
Registered: October 2006
Location: UK
Member
I am not very sure but may be it can help you with less number of records.

select id,name from l_table
minus
select id,name from m_table
union
select 'Diff of m_table','and l_table' from dual
UNION ALL
select id,name from m_table
minus
select id,name from l_table

[Updated on: Fri, 20 October 2006 08:53]

Report message to a moderator

Previous Topic: performance issue on fetching values from an xml file stored into a CLOB column in DB (merged 2 thre
Next Topic: sequences
Goto Forum:
  


Current Time: Mon Dec 05 04:38:33 CST 2016

Total time taken to generate the page: 0.07314 seconds