Hello,

I'm trying to determine the negation of A intersect B quicker than:

(A UNION B) MINUS (A INTERSECT B)

Is there a standard SQL operator that will do this?

Thanks

cdecarlo (AT) gmail (DOT) com wrote:
Subselect using NOT IN? Such as

SELECT * FROM B WHERE B.id NOT IN (
SELECT id FROM A
)

Not tested. It seems to be an equivalent approach, but I am not entirely
sure.

Regards,

Michael.
--
Which is more dangerous? TV guided missiles or TV guided families?
I am less likely to answer usenet postings by anonymous authors.
Visit my home page at http://michael.zedeler.dk/

Thanks for your reply Mike but I don't think that is an equivilant
approach. Your query will return the items in B that aren't in A,
whereas I'm trying to get the items that are in A or B but not in both
A and B. Hope that helps clear things up, if you would like to take
another kick at the can.

-Colin

Michael Zedeler wrote:

In article <1150737539.920386.275540 (AT) u72g2000cwu (DOT) googlegroups.com>,
cdecarlo (AT) gmail (DOT) com writes:

How about a FULL OUTER JOIN?

Thanks for the reply Harald but a full outer join will provide me much
more information than what I am looking for. The items that I want are
the outliers of the datasets, in other words, the items that the
datasets don't have in common. The query that I have written so far
is:

(
SELECT attribute
FROM a
UNION
SELECT attribute
FROM b
)
MINUS
(
SELECT attribute
FROM a
INTERSECT
SELECT attribute
FROM b
)

This works fine but I think that it is cumbesome and I have no idea how
scaleable it is. I suppose a full outer join would clean up the first
part of the query (before the MINUS) but I would stll have to subtract
the intersection. ie.

SELECT attribute
from a full outer join b
on a.attribute = b.attribute
MINUS
(
SELECT attribute
FROM a
INTERSECT
SELECT attribute
FROM b
)

Anyways, I'm still open to suggestions.

-Colin

Harald Fuchs wrote:

Thanks for the reply Harald but a full outer join will provide me much
more information than what I am looking for. The items that I want are
the outliers of the datasets, in other words, the items that the
datasets don't have in common. The query that I have written so far
is:

(
SELECT attribute
FROM a
UNION
SELECT attribute
FROM b
)
MINUS
(
SELECT attribute
FROM a
INTERSECT
SELECT attribute
FROM b
)

This works fine but I think that it is cumbesome and I have no idea how
scaleable it is. I suppose a full outer join would clean up the first
part of the query (before the MINUS) but I would stll have to subtract
the intersection. ie.

SELECT attribute
from a full outer join b
on a.attribute = b.attribute
MINUS
(
SELECT attribute
FROM a
INTERSECT
SELECT attribute
FROM b
)

Anyways, I'm still open to suggestions.

-Colin

Harald Fuchs wrote:

cdecarlo (AT) gmail (DOT) com wrote:
Of course. Didn't read the expression carefully enough to se that it is
symmetric, but I did read your text and got confused.

I don't see how you would be able to boil it further down, but again,
that is not a very useful answer. Will think a little more about it.

Regards,

Michael.
--
Which is more dangerous? TV guided missiles or TV guided families?
I am less likely to answer usenet postings by anonymous authors.
Visit my home page at http://michael.zedeler.dk/

Michael Zedeler wrote:

cdecarlo (AT) gmail (DOT) com wrote:
The standard SQL operator is EXCEPT.
But it's not implemented in all RDBMS products.
Oracle appears to use the keyword MINUS instead of EXCEPT.

Regards,
Bill K.

cdecarlo (AT) gmail (DOT) com wrote:
data.

SQL> CREATE TABLE a (a NUMBER);

Table created.

SQL> CREATE TABLE b (a NUMBER);

Table created.

SQL> INSERT INTO a SELECT rownum FROM all_objects WHERE rownum <6;

5 rows created.

SQL> INSERT INTO b SELECT rownum + 3 FROM all_objects WHERE rownum <6;

5 rows created.

SQL> SELECT * FROM a;

A
----------
1
2
3
4
5

SQL> SELECT * FROM b;

A
----------
4
5
6
7
8

Your query would be as follows:
SQL> SELECT * FROM ((
2 SELECT * FROM a
3 UNION
4 SELECT * FROM b)
5 MINUS (
6 SELECT * FROM a
7 INTERSECT
8 SELECT * FROM b
9 ));

A
----------
1
2
3
6
7
8

6 rows selected.

Changed query:
SQL> SELECT * FROM (
2 SELECT DISTINCT a FROM a
3 UNION ALL
4 SELECT DISTINCT a FROM b
5 )
6 GROUP BY a
7 HAVING count(*) = 1;

A
----------
1
2
3
6
7
8

6 rows selected.

And now let's look at explain plans and statistics:
SQL> set autotrace traceonly
SQL> SELECT * FROM ((
2 SELECT * FROM a
3 UNION
4 SELECT * FROM b)
5 MINUS (
6 SELECT * FROM a
7 INTERSECT
8 SELECT * FROM b
9 ));

6 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=1 Bytes=1
3)

1 0 VIEW (Cost=1 Card=1 Bytes=13)
2 1 MINUS
3 2 SORT (UNIQUE) (Cost=60 Card=164 Bytes=4264)
4 3 UNION-ALL
5 4 TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=82 Bytes=1
066)

6 4 TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=82 Bytes=1
066)

7 2 INTERSECTION
8 7 SORT (UNIQUE) (Cost=15 Card=82 Bytes=1066)
9 8 TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=82 Bytes=1
066)

10 7 SORT (UNIQUE) (Cost=15 Card=82 Bytes=1066)
11 10 TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=82 Bytes=1
066)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
467 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
6 rows processed

SQL> SELECT * FROM (
2 SELECT DISTINCT a FROM a
3 UNION ALL
4 SELECT DISTINCT a FROM b
5 )
6 GROUP BY a
7 HAVING count(*) = 1;

6 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=42 Card=164 Byte
s=2132)

1 0 FILTER
2 1 SORT (GROUP BY) (Cost=42 Card=164 Bytes=2132)
3 2 VIEW (Cost=30 Card=164 Bytes=2132)
4 3 UNION-ALL
5 4 SORT (UNIQUE) (Cost=15 Card=82 Bytes=1066)
6 5 TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=82 Bytes
=1066)

7 4 SORT (UNIQUE) (Cost=15 Card=82 Bytes=1066)
8 7 TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=82 Bytes
=1066)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
467 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>

4 full scans and 3 sorts vs 2 full scans and 3 sorts and 28 consistent
gets vs 14 consistent gets.
Of course I used DISTINCT just to show that in general case you should
use it.

Gints Plivna
http://www.gplivna.eu/