Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to find the rows that are different between a table and it's sub view.
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