Adding Joins to a Query Without Interfering with Other Joins - SQL

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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table