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: <bay_dar_at_yahoo.com>
Date: Tue, 12 Jun 2007 10:23:34 -0700
Message-ID: <1181669014.374827.35290@i38g2000prf.googlegroups.com>


On Jun 12, 12:35 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jun 12, 12:06 pm, bay_..._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.
>
> > For instance, the tables have the following values, where the IP and
> > PORT are PK's:
>
> > Table A
> > IP PORT
> > 127.0.0.1 55
> > 127.0.0.2 55
> > 127.0.0.5 22
> > 127.0.0.2 33
> > 127.0.0.2 36
> > 127.0.0.6 55
>
> > Table B
> > IP PORT
> > 127.0.0.1 55
> > 127.0.0.2 55
> > 127.0.0.5 22
>
> > The question is how can I write a query to produce the last 3 rows in
> > Table A that are not in Table B?
>
> > Thanks.
>
> You will likely have better performance if you directly use the SQL
> statement behind the view, rather than just using the view name. For
> instance, if TABLE_B is actually:

Thanks, I modified your suggestion:

SELECT
  A.IP,
  A.PORT
FROM
  TABLE_A A
  (SELECT
    IP,
    PORT
  FROM
    TABLE_A
  WHERE
    IP='127.0.0.1') B
WHERE
  A.IP=B.IP(+)
  AND A.PORT=B.PORT(+)
  AND B.IP IS NULL; to

SELECT
  A.IP,
  A.PORT
FROM
  TABLE_A TABLE_B
WHERE
  A.IP=B.IP(+)
  AND A.PORT=B.PORT(+)
  AND B.IP IS NULL; and it worked great. Received on Tue Jun 12 2007 - 12:23:34 CDT

Original text of this message

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