WHERE: List vs. subselect

Hi,
we're having a little problem with outer joins - using a subselect seems not to be equivalent to specifying a list of values in the where-clause. Using EMP/DEPT, here's our problem:

SQL> SELECT DISTINCT d.deptno
  2 FROM scott.dept D
  3 /

    DEPTNO

        10
        20
        30
        40

4 rows selected.

SQL> SELECT e.deptno, NVL( SUM( e.sal ), '9999999' ) summe   2 FROM scott.emp E
  3 WHERE e.deptno (+) IN

  4        ( SELECT  DISTINCT d.deptno
  5          FROM    scott.dept D )

    DEPTNO SUMME
---------- ----------

        10       8750
        20      10875
        30       9400
              9999999

4 rows selected.

This is what I expected (and close to the given example in the SQL Language Reference Manual).

SQL> SELECT e.deptno, NVL( SUM( e.sal ), '9999999' ) summe   2 FROM scott.emp E
  3 WHERE e.deptno (+) IN ( 10, 20, 30, 40 )   4 GROUP BY e.deptno
  5 /

    DEPTNO SUMME
---------- ----------

        10       8750
        20      10875
        30       9400

3 rows selected.

But this isn't!

Obviously, the outer join won't work when specifying a list of values instead of using a subselect. Up to now, I thought, they were equivalent... Is there a workaround to this, i.e. how can I achieve the expected behaviour (without creating a table which holds the known values)? BTW: Using 6.0.36.3.1 on SunOS 4.1.3.

Christian
---

No mail >16 KB, please. Received on Tue Mar 29 1994 - 18:23:00 CEST