OraFAQ Forum: SQL & PL/SQL » Query that will compare two tables row by row and returns distinct rows from both tables

Home » SQL & PL/SQL » SQL & PL/SQL » Query that will compare two tables row by row and returns distinct rows from both tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query that will compare two tables row by row and returns distinct rows from both tables [message #611341]

Tue, 01 April 2014 13:20

Alexey.Samoilov
Messages: 11
Registered: April 2014

Junior Member

query that will compare two tables row by row and returns distinct rows from both tables.
My variant compare names, but i don't know how compare rows

SELECT * FROM tab1
FULL OUTER JOIN tab2
ON tab1.name = tab2.name
WHERE tab1.id IS null 
OR tab2.id IS null;

Report message to a moderator

Re: Query that will compare two tables row by row and returns distinct rows from both tables [message #611343 is a reply to message #611341]

Tue, 01 April 2014 13:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

SELECT * FROM TAB1
MINUS
SELECT * FROM TAB2
UNION
SELECT * FROM TAB2
MINUS
SELECT * FROM TAB1;

Report message to a moderator

Re: Query that will compare two tables row by row and returns distinct rows from both tables [message #611344 is a reply to message #611341]

Tue, 01 April 2014 13:29

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

See Join wiki page.

Report message to a moderator

Re: Query that will compare two tables row by row and returns distinct rows from both tables [message #611350 is a reply to message #611344]

Tue, 01 April 2014 13:54

Alexey.Samoilov
Messages: 11
Registered: April 2014

Junior Member

Thanks!)

Report message to a moderator

Re: Query that will compare two tables row by row and returns distinct rows from both tables [message #611355 is a reply to message #611343]

Tue, 01 April 2014 15:00

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Operator precedence!!!

SELECT * FROM TAB1
MINUS
SELECT * FROM TAB2
UNION ALL
(SELECT * FROM TAB2
MINUS
SELECT * FROM TAB1
);

Also, it will not catch same row is in both tables but different number of times. To catch that we need to add something like analytic ROW_NUMBER.

SY.

Report message to a moderator

Previous Topic:	raise error propagation
Next Topic:	Grant Create Table On few tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 17:11:29 CDT 2024