Recipe3.6.Adding Joins to a Query Without Interfering with Other Joins

Recipe 3.6. Adding Joins to a Query Without Interfering with Other Joins

Problem

You have a query that returns the results you want. You need additional information, but when trying to get it, you lose data from the original result set. For example, you want to return all employees, the location of the department in which they work, and the date they received a bonus. For this problem, the EMP_BONUS table contains the following data:

  select * from emp_bonus       EMPNO  RECEIVED          TYPE  ----------  ----------- ----------        7369  14-MAR-2005          1        7900  14-MAR-2005          2        7788  14-MAR-2005          3 

The query you start with looks like this:

  select e.ename, d.loc   from emp e, dept d  where e.deptno=d.deptno   ENAME      LOC   ---------- -------------   SMITH      DALLAS   ALLEN      CHICAGO   WARD       CHICAGO   JONES      DALLAS   MARTIN     CHICAGO   BLAKE      CHICAGO   CLARK      NEW YORK   SCOTT      DALLAS   KING       NEW YORK   TURNER     CHICAGO   ADAMS      DALLAS   JAMES      CHICAGO   FORD       DALLAS   MILLER     NEW YORK 

You want to add to these results the date a bonus was given to an employee, but joining to the EMP_BONUS table returns fewer rows than you wish because not every employee has a bonus:

  select e.ename, d.loc,eb.received   from emp e, dept d, emp_bonus eb  where e.deptno=d.deptno    and e.empno=eb.empno ENAME       LOC           RECEIVED ----------  ------------- ----------- SCOTT       DALLAS        14-MAR-2005 SMITH       DALLAS        14-MAR-2005 JAMES       CHICAGO       14-MAR-2005 

Your desired result set is the following:

 ENAME       LOC            RECEIVED ----------  -------------  ----------- ALLEN       CHICAGO WARD        CHICAGO MARTIN      CHICAGO JAMES       CHICAGO        14-MAR-2005 TURNER      CHICAGO BLAKE       CHICAGO SMITH       DALLAS         14-MAR-2005 FORD        DALLAS ADAMS       DALLAS JONES       DALLAS SCOTT       DALLAS         14-MAR-2005 CLARK       NEW YORK KING        NEW YORK MILLER      NEW YORK 

Solution

You can use an outer join to obtain the additional information without losing the data from the original query. First join table EMP to table DEPT to get all employees and the location of the department they work, then outer join to table EMP_ BONUS to return the date of the bonus if there is one. Following is the DB2, MySQL, PostgreSQL, and SQL Server syntax:

 1 select e.ename, d.loc, eb.received 2   from emp e join dept d 3     on (e.deptno=d.deptno) 4   left join emp_bonus eb 5     on (e.empno=eb.empno) 6  order by 2 

If you are using Oracle9i Database or later, the preceding solution will work for you. Alternatively, you can use Oracle's proprietary outer-join syntax, which is your only choice when using Oracle8i Database and earlier:

 1 select e.ename, d.loc, eb.received 2   from emp e, dept d, emp_bonus eb 3  where e.deptno=d.deptno 4    and e.empno=eb.empno (+) 5  order by 2 

You can also use a scalar subquery (a subquery placed in the SELECT list) to mimic an outer join:

 1 select e.ename, d.loc, 2        (select eb.received from emp_bonus eb 3          where eb.empno=e.empno) as received 4   from emp e, dept d 5  where e.deptno=d.deptno 6  order by 2 

The scalar subquery solution will work across all platforms.

Discussion

An outer join will return all rows from one table and matching rows from another. See the previous recipe for another example of such a join. The reason an outer join works to solve this problem is that it does not result in any rows being eliminated that would otherwise be returned. The query will return all the rows it would return without the outer join. And it also returns the received date, if one exists.

Use of a scalar subquery is also a convenient technique for this sort of problem, as it does not require you to modify already correct joins in your main query. Using a scalar subquery is an easy way to tack on extra data to a query without compromising the current result set. When working with scalar subqueries, you must ensure they return a scalar (single) value. If a subquery in the SELECT list returns more than one row, you will receive an error.

See Also

See "Converting a Scalar Subquery to a Composite Subquery in Oracle" in Chapter 14 for a workaround to the problem of not being able to return multiple rows from a SELECT-list subquery.