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

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:
CREATE VIEW TABLE_B AS
SELECT
  *
FROM
  TABLE_A
WHERE
  IP='127.0.0.1';

You would instead use the above SQL directly in the other SQL statements. For instance, if the original SQL statement looked like this:
SELECT
  IP,
  PORT
FROM
  TABLE_A
UNION ALL
SELECT
  IP,
  PORT
FROM
  TABLE_B; The above finds all rows in common between the two tables (one table and one view actually). Rather than using the above, I would write it like this:
SELECT
  IP,
  PORT
FROM
  TABLE_A
UNION ALL
SELECT
  IP,
  PORT
FROM
  TABLE_A
WHERE
  IP='127.0.0.1';

You are not looking for the rows in common, but instead the rows that are in the first data set, but not in the second - the MINUS syntax would be used for this:
SELECT
  IP,
  PORT
FROM
  TABLE_A
MINUS
SELECT
  IP,
  PORT
FROM
  TABLE_A
WHERE
  IP='127.0.0.1';

If you do not like MINUS, you can write the SQL statement like this: 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; Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jun 12 2007 - 11:35:13 CDT