Recipe3.2.Combining Related Rows

Recipe 3.2. Combining Related Rows

Problem

You want to return rows from multiple tables by joining on a known common column or joining on columns that share common values. For example, you want to display the names of all employees in department 10 along with the location of each employee's department, but that data is stored in two separate tables. You want the result set to be the following:

 ENAME       LOC ----------  ---------- CLARK       NEW YORK KING        NEW YORK MILLER      NEW YORK 

Solution

Join table EMP to table DEPT on DEPTNO:

 1 select e.ename, d.loc 2   from emp e, dept d 3  where e.deptno = d.deptno 4    and e.deptno = 10 

Discussion

The solution is an example of a join, or more accurately an equi-join, which is a type of inner join. A join is an operation that combines rows from two tables into one. An equi-join is one in which the join condition is based on an equality condition (e.g., where one department number equals another). An inner join is the original type of join; each row returned contains data from each table.

Conceptually, the result set from a join is produced by first creating a Cartesian product (all possible combinations of rows) from the tables listed in the FROM clause, as seen below:

  select e.ename, d.loc,        e.deptno as emp_deptno,        d.deptno as dept_deptno   from emp e, dept d  where e.deptno = 10 ENAME      LOC            EMP_DEPTNO DEPT_DEPTNO ---------- -------------  ---------- ----------- CLARK      NEW YORK               10          10 KING       NEW YORK               10          10 MILLER     NEW YORK               10          10 CLARK      DALLAS                 10          20   KING       DALLAS                 10          20 MILLER     DALLAS                 10          20 CLARK      CHICAGO                10          30 KING       CHICAGO                10          30 MILLER     CHICAGO                10          30 CLARK      BOSTON                 10          40 KING       BOSTON                 10          40 MILLER     BOSTON                 10          40 

Every employee in table EMP (in department 10) is returned along with every department in the table DEPT. Then, the expression in the WHERE clause involving e.deptno and d.deptno (the join) restricts the result set such that the only rows returned are the ones where EMP.DEPTNO and DEPT.DEPTNO are equal:

  select e.ename, d.loc,        e.deptno as emp_deptno,        d.deptno as dept_deptno   from emp e, dept d  where e.deptno = d.deptno    and e.deptno = 10 ENAME       LOC             EMP_DEPTNO  DEPT_DEPTNO ----------  --------------  ----------  ----------- CLARK       NEW YORK                10           10 KING        NEW YORK                10           10 MILLER      NEW YORK                10           10 

An alternative solution makes use of an explicit JOIN clause (the "INNER" keyword is optional):

 select e.ename, d.loc   from emp e inner join dept d     on (e.deptno = d.deptno)  where e.deptno = 10 

Use the JOIN clause if you prefer to have the join logic in the FROM clause rather than the WHERE clause. Both styles are ANSI compliant and work on all the latest versions of the RDBMSs in this book.