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.
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