11 September, 2011

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 ?

.
.
.

2 comments:

patrick@bar-solutions.com said...

Using ANSI SQL would be something like this:
select d.deptno, e.ename
from dept d left outer join emp e on (d.deptno=e.deptno)

Regards,
Patrick

Hemant K Chitale said...

Patrick,
Thank you.

Hemant