Outer Join Queries

Outer Join queries are difficult to understand initially. Effectively, an Outer Join query allows the developer to create dummy rows (with NULLs for the returned columns) for a table when a join to another table would fail. Thus, the developer can see rows in the other table for which there are no corresponding entries in the "join failed" table.

A simple example (from the classic SCOTT schema) is a DEPTNO 40 which has been created in the DEPT table but for which no employees exist in the EMP table. Thus a join between DEPT and EMP on DEPTNO would, normally, never return the row for DEPTNO=40 from the DEPT table because the join fails. An OuterJoin definition against EMP allows this row to be retrieved from DEPT with a "dummy" row (and NULL values) from the EMP table.

In how many ways can Outer Joins be achieved ? Here I present an alternative that does not have to use the Outer Join (+) syntax :

SQL> select deptno from dept;

    DEPTNO
----------
        10
        20
        30
        40

SQL> select deptno, count(*) from emp group by deptno order by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6

SQL> 
SQL> -- dept 40 exists in dept but no employees present in emp
SQL> 
SQL> REM Other ways to write Outer Joins
SQL> 
SQL> -- most common way
SQL> select d.deptno, nvl(e.ename,'No employee')
  2  from dept d, emp e
  3  where d.deptno=e.deptno(+)
  4  order by deptno
  5  /

    DEPTNO NVL(E.ENAME
---------- -----------
        10 CLARK
        10 KING
        10 MILLER
        20 JONES
        20 FORD
        20 ADAMS
        20 SMITH
        20 SCOTT
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 BLAKE
        30 MARTIN
        40 No employee

15 rows selected.

SQL> 
SQL> -- another way
SQL> select d.deptno, e.ename
  2  from dept d, emp e
  3  where d.deptno=e.deptno
  4  union all
  5  select d.deptno,'No employee'
  6  from dept d
  7  where deptno not in (select distinct e.deptno from emp e)
  8  order by deptno
  9  /

    DEPTNO ENAME
---------- -----------
        10 CLARK
        10 MILLER
        10 KING
        20 JONES
        20 SMITH
        20 SCOTT
        20 FORD
        20 ADAMS
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 MARTIN
        30 BLAKE
        40 No employee

15 rows selected.

SQL> 

How would you write this query using ANSI syntax ? Are there any other ways to fetch the result set ?

.
.
.