Re: [sqr-users] Problem with UNION Clause in SQR( withpositionalparameters)

["George Jansen" <GJANSEN@aflcio.org>]

Not really SQR issues, SQL issues, but addressing them could make the
SQR problems easier to see:

1. Oracle will already throw away duplicates in a union unless you
specify UNION ALL. In other words you don't need to say DISTINCT.  See
example 1 below.

2. I'm trying but failing to imagine what the UNION practically
accomplishes. If you run
SELECT * FROM superset
UNION
SELECT * FROM subset
Logically the result must be the same as SELECT * FROM superset. See
example 2 below.

3. What is the point of using an outer join when no columns from the
outer join find their way into the result set? See example 3 below.


EXAMPLE 1:
SQL> select * from bogus;

AL
--
a
a
a
a

SQL> select * from bogus
  2  union
  3  select * from bogus;

AL
--
a

EXAMPLE 2:
SQL> GET /tmp/bogoid2
  1  SELECT * FROM
  2  (
  3   (SELECT e.*
  4    FROM emp e, dept d
  5    WHERE e.deptno = d.deptno)
  6   MINUS
  7   (SELECT e.*
  8    FROM emp e, dept d
  9    WHERE e.deptno = d.deptno
 10    UNION
 11    SELECT e.*
 12    FROM emp e, dept d
 13    WHERE e.deptno = d.deptno
 14      AND d.loc = 'CHICAGO'
 15   )
 16* )
SQL> /

no rows selected

EXAMPLE 3:
SQL> SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP;

    DEPTNO
----------
        40

SQL> get /tmp/bogoid3.sql
  1  SELECT * FROM
  2  (
  3   SELECT e.*
  4   FROM emp e, dept d
  5   WHERE e.deptno = d.deptno(+)
  6   MINUS
  7   SELECT e.*
  8   FROM emp e, dept d
  9   WHERE e.deptno = d.deptno
 10* )
SQL> /

no rows selected


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users