How to add joins to a query without interfering with other joins in SQL?

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

Department Table

deptno dname loc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Staff_bonus Table

empno received type
7369 14-MAR-2005 1
7900 14-MAR-2005 2
7788 14-MAR-2005 3

You have a query that yields the desired results. You require more details, yet attempting to obtain them causes you to lose information from the initial set of results. For instance, you might wish to retrieve a list of every staff along with their department, job title, and bonus date.

The query you start with looks like this:

SELECT s.ename, d.loc
from Staff s, Department d
WHERE s.deptno=d.deptno

Output

ename loc
ALI DALLAS
ALLEN CHICAGO
WARD CHICAGO
JONES DALLAS
MARTIN CHICAGO
BLAKE CHICAGO
CLARK NEW YORK
RAHUL DALLAS
RAJ NEW YORK
TURNER CHICAGO
ADAM DALLAS
JAMES CHICAGO
ARHAM DALLAS
MILLER NEW YORK

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

SELECT s.ename, d.loc,sb.received
FROM Staff s, dept d, Staff_bonus sb
WHERE s.deptno=d.deptno
AND s.empno=sb.empno

Output

ename loc received
ALI DALLAS 14-MAR-2005
RAHUL DALLAS 14-MAR-2005
JAMES CHICAGO 14-MAR-2005

Your desired result set is the following:

empno 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

and to get the desired result you can use an outer join to obtain the additional information without losing the data from the original query. First join table Staff to table Department to get all staff and the location of the department they work, then outer join to table Staff_bonus to return the date of the bonus if there is one. The following is the DB2, MySQL, PostgreSQL, and SQL server syntax:

SELECT s.ename, d.loc, sb.received
FROM Staff s join Department d
ON (s.deptno=d.deptno)
LEFT join Staff_bonus sb
ON (s.empno=sb.empno)
ORDER BY 2

Output

empno 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

Discussion

All rows from one table and any matching rows from another will be returned by an outer join. Because no rows that would otherwise be returned are lost while using an outside join to fix this issue, it is effective. All of the rows that the query would have returned without the outer join are returned. Additionally, if there is one, it returns the received date.