Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to find the rows that are different between a table and it's sub view.

Re: How to find the rows that are different between a table and it's sub view.

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 12 Jun 2007 13:37:20 -0800
Message-ID: <466f0400$1@news.victoria.tc.ca>


bay_dar_at_yahoo.com wrote:
: Hi, I'm trying to write a sql statement to find the rows that present
: in table A but not in table B. Actually Table B is a view of A, so
: all values of B are guaranteed to be in Table A (but not the other way
: around). But I've never written onc of these reverse kind of queries
: and could use some advice.

Assuming the view has the same columns

select * from A
minus
select * from B

or copy the logic from view B and put a NOT in front

so for example if B were

	create or replace view B as
	select * from A
	where flag1='Y' and num2 > 10

then

	select * from A
	where NOT ( flag1='Y' and num2 > 10 )

(It would be interesting to figure if that is any more efficient than simply doing the minus). Received on Tue Jun 12 2007 - 16:37:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US