(A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B)

#11

Quote:

Is there a standard SQL operator that will do this?

SQL-92 defined an OUTER UNION, but nobody implemented it. This is the
best you will do if you want to use general tables names and have the
options of adding ALL to your set operators.

#12

In article <1150811088.834378.247280 (AT) i40g2000cwc (DOT) googlegroups.com>,
"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> writes:

Quote:

Is there a standard SQL operator that will do this?

SQL-92 defined an OUTER UNION, but nobody implemented it.

Huh?

CREATE TEMPORARY TABLE t1 (
id INT NOT NULL,
PRIMARY KEY (id)
);

COPY t1 FROM stdin;
1
2
3
4
\.

CREATE TEMPORARY TABLE t2 (
id INT NOT NULL,
PRIMARY KEY (id)
);

COPY t2 FROM stdin;
3
4
5
6
\.

SELECT t1.id AS t1id, t2.id AS t2id
FROM t1
FULL OUTER JOIN t2 ON t2.id = t1.id;

returns

t1id | t2id
------+------
1 |
2 |
3 | 3
4 | 4

Quote:

5
6
(6 rows)

on PostgreSQL 8.1. Isn't that what you mean?

#13

FULL OUTER JOIN <> OUTER UNION

#14

Wow,

I am completely bamboozaled by that reply, thanks though, it sure is
humbling; I've got a lot to learn.

-Colin

Gints Plivna wrote:

Quote:

cdecarlo (AT) gmail (DOT) com wrote:
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
Probably in this way. At first createing some tables and feeding with
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/

#15

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote:

Quote:

FULL OUTER JOIN <> OUTER UNION

Care to explain the difference? I thought that the second was simply a
shorthand for the first.

TIA.

Paul...

BTW, am enjoying your SQL for smarties book.

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

#16

Paul wrote:

Quote:

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote:

FULL OUTER JOIN <> OUTER UNION

Care to explain the difference? I thought that the second was simply a
shorthand for the first.

I think it is as follows...

Given two tables, t1 with columns a and b, and t2 with columns c and d,
FULL OUTER JOIN will produce a table with four columns, a,b,c and d,
some of which will contain nulls where no match occurs between then
tables. OUTER UNION will produce a table with two columns. The table
will consist of those rows from t1 for which there is no matching row
in t2, and those rows from t2 for which there is no matching row in t1.

Damien

#17

If tables have primary key say ka and kb.
How about this?

SELECT *
FROM a
WHERE NOT EXISTS
(SELECT *
FROM b
WHERE b.kb = a.ka
)
UNION
SELECT *
FROM b
WHERE NOT EXISTS
(SELECT *
FROM a
WHERE a.ka = b.kb
)

#18

Damien wrote:

Quote:

Given two tables, t1 with columns a and b, and t2 with columns c and d,
FULL OUTER JOIN will produce a table with four columns, a,b,c and d,
some of which will contain nulls where no match occurs between then
tables. OUTER UNION will produce a table with two columns. The table
will consist of those rows from t1 for which there is no matching row
in t2, and those rows from t2 for which there is no matching row in t1.

This is really confusing. The commonly accepted mathematical term for

(A\B) U (B\A)

or equivalently

(AUB) / (B/\A)

is *symmetric difference*.

#19

Paul wrote:

Quote:

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote:

FULL OUTER JOIN <> OUTER UNION

Care to explain the difference? I thought that the second was simply a
shorthand for the first.

Outer Union

The union operator definition in Relational Algebra has a problem. It
is can be applied only to compatible relations that have identical
attributes. The Outer Union operator, invented by E.F.Codd , can be
applied to any pair of relations. Each relation is extended to the
schema that contains all the attributes from both relations. The newly
introduced columns are padded with NULLs. The resulting relations have
the same schema and their tuples can, therefore, be combined together
by (ordinary) union.

Example. Suppose we have the Personnel relation
Dept Emp
10 Smith
20 Jones
and the Department
Dept Mgr
20 Blake
30 James
The outer union Personnel ⊎ Department is
Dept Emp Mgr
10 Smith null
20 Jones null
20 null Blake
30 null James

This operator seems to have extremely limited practical scope. The only
usage of the outer union operator I’m aware of is the definition of
(the full) outer join. Formally, outer join of the relations A and B is
defined in four steps:
1. Join both relations: A wv B. In the example above we’ll have
Dept Emp Mgr
20 Jones Blake

2. Join both relations, project to the first relation schema and
subtract the result from the first relation: A \ psch(A) (A wv B)
Dept Emp
10 Smith

3. Join both relations, project to the first relation schema and
subtract the result from the first relation: B \ psch(B) (A wv B)
Dept Mgr
30 James

4. Apply outer union to the results of 1-3.
Dept Emp Mgr
10 Smith null
20 Jones Blake
30 null James

What we have just discussed are actually the relational algebra
versions of the outer union and outer join operations. Perhaps they
should be properly named natural outer union and natural outer join. In
SQL the two relations are always considered to have disjoint sets of
attributes. In our example, the outer union produces
Personnel.Dept Department.Dept Emp Mgr
10 null Smith null
20 null Jones null
null 20 null Blake
null 30 null James
and outer join
Personnel.Dept Department.Dept Emp Mgr
10 null Smith null
20 20 Jones Blake
null 30 null James
The formula connecting them is still valid, though

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

(A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B)

comp.databases comp.databases

Re: (A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B) - 06-20-2006 , 08:44 AM

Re: (A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B) - 06-20-2006 , 11:38 AM

Re: (A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B) - 06-20-2006 , 10:31 PM

Re: (A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B) - 06-21-2006 , 07:57 AM

Re: (A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B) - 06-21-2006 , 03:44 PM

Re: (A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B) - 06-22-2006 , 02:41 AM

Re: (A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B) - 07-18-2006 , 04:25 AM

Re: (A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B) - 07-18-2006 , 12:22 PM

Re: (A UNION B) MINUS (A INTERSECT B) = not(A INTERSECT B) - 07-18-2006 , 12:30 PM