December 2004 Technical Tip – Outer Joins, ON, WHERE and AND

December 2004 Technical Tip – Outer Joins, ON, WHERE and AND

In this article I will introduce the use of outer joins in SQL, with particular emphasis on the placement of the and operator, in conjunction with the on and where clauses. I will refer to the following tables in this article. The EMPL tables contains employee data and the THISPP table contains the hours worked by each employee this pay period:

mysql> select * from empl;
+------+--------+--------+------+--------+------+------------+
| enum | lname  | fname  | dept | rate   | type | hire       |
+------+--------+--------+------+--------+------+------------+
| 211  | DAYLEY | BRIAN  | A    |   6.00 | H    | 1991-07-26 |
| 270  | DAYLEY | CINDY  | B    | 300.00 | S    | 1990-04-11 |
| 601  | STEELE | DOUG   | A    |   5.00 | H    | 1992-06-30 |
| 719  | ALFORD | BRIAN  | B    |   4.50 | H    | 1992-12-31 |
| 828  | GOYAK  | DEBBIE | A    | 320.00 | S    | 1989-11-08 |
| 857  | ALFORD | MARK   | B    |   5.00 | H    | 1992-12-27 |
+------+--------+--------+------+--------+------+------------+


mysql> select * from thispp;
+------+-------+
| enum | hours |
+------+-------+
| 601  | 30.00 |
| 828  | 42.00 |
| 270  | 35.00 |
| 211  | 25.00 |
| 719  | 30.00 |
+------+-------+

Note that employee 857 did not work this pay period and therefore is not found in the THISPP table. This is critical to the following discussion.

The traditional join used in SQL is more correctly known as an inner join. Recall that in this type of join, a where clause is used to indicate rows to be returned based upon values which the base tables have in common. For example:

mysql> select e.enum, e.lname, e.fname, e.dept, e.rate, e.type, p.hours
    -> from empl e, thispp p
    -> where e.enum = p.enum;
+------+--------+--------+------+--------+------+-------+
| enum | lname  | fname  | dept | rate   | type | hours |
+------+--------+--------+------+--------+------+-------+
| 601  | STEELE | DOUG   | A    |   5.00 | H    | 30.00 |
| 828  | GOYAK  | DEBBIE | A    | 320.00 | S    | 42.00 |
| 270  | DAYLEY | CINDY  | B    | 300.00 | S    | 35.00 |
| 211  | DAYLEY | BRIAN  | A    |   6.00 | H    | 25.00 |
| 719  | ALFORD | BRIAN  | B    |   4.50 | H    | 30.00 |
+------+--------+--------+------+--------+------+-------+

An outer join returns unmatched rows as well. In a full outer join, all rows from all tables – matched as well as unmatched – are returned at least once. In a left outer join or right outer join all rows from the designated table are returned at least once. When an outer join is used, the on clause is used to indicate which rows are returned from the join. For example:

mysql> select e.enum, e.lname, e.fname, e.dept, e.rate, e.type, p.hours
    -> from empl e left outer join thispp p on e.enum = p.enum;
+------+--------+--------+------+--------+------+-------+
| enum | lname  | fname  | dept | rate   | type | hours |
+------+--------+--------+------+--------+------+-------+
| 211  | DAYLEY | BRIAN  | A    |   6.00 | H    | 25.00 |
| 270  | DAYLEY | CINDY  | B    | 300.00 | S    | 35.00 |
| 601  | STEELE | DOUG   | A    |   5.00 | H    | 30.00 |
| 719  | ALFORD | BRIAN  | B    |   4.50 | H    | 30.00 |
| 828  | GOYAK  | DEBBIE | A    | 320.00 | S    | 42.00 |
| 857  | ALFORD | MARK   | B    |   5.00 | H    |  NULL |
+------+--------+--------+------+--------+------+-------+

The where clause can be used to further reduce the rows returned by the query. For example:

mysql> select e.enum, e.lname, e.fname, e.dept, e.rate, e.type, p.hours
    -> from empl e left outer join thispp p on e.enum = p.enum
    -> where e.type = 'H';
+------+--------+-------+------+------+------+-------+
| enum | lname  | fname | dept | rate | type | hours |
+------+--------+-------+------+------+------+-------+
| 211  | DAYLEY | BRIAN | A    | 6.00 | H    | 25.00 |
| 601  | STEELE | DOUG  | A    | 5.00 | H    | 30.00 |
| 719  | ALFORD | BRIAN | B    | 4.50 | H    | 30.00 |
| 857  | ALFORD | MARK  | B    | 5.00 | H    |  NULL |
+------+--------+-------+------+------+------+-------+

The and operator can be used with the on clause as well as with the where clause. Its placement is signficant: the key point to remember is that the where clause subsets the individual detail rows before the outer join is performed. The on clause then specifies how the remaining rows are to be selected for output. In the following queries we add the constraint E.DEPT = 'B'. The two queries differ only in their placement of the and, and yet the results are significantly different:

mysql> select e.enum, e.lname, e.fname, e.dept, e.rate, e.type, p.hours
    -> from empl e left outer join thispp p on e.enum = p.enum and e.dept = 'B'
    -> where e.type = 'H';
+------+--------+-------+------+------+------+-------+
| enum | lname  | fname | dept | rate | type | hours |
+------+--------+-------+------+------+------+-------+
| 211  | DAYLEY | BRIAN | A    | 6.00 | H    |  NULL |
| 601  | STEELE | DOUG  | A    | 5.00 | H    |  NULL |
| 719  | ALFORD | BRIAN | B    | 4.50 | H    | 30.00 |
| 857  | ALFORD | MARK  | B    | 5.00 | H    |  NULL |
+------+--------+-------+------+------+------+-------+


mysql> select e.enum, e.lname, e.fname, e.dept, e.rate, e.type, p.hours
    -> from empl e left outer join thispp p on e.enum = p.enum
    -> where e.type = 'H' and e.dept = 'B';
+------+--------+-------+------+------+------+-------+
| enum | lname  | fname | dept | rate | type | hours |
+------+--------+-------+------+------+------+-------+
| 719  | ALFORD | BRIAN | B    | 4.50 | H    | 30.00 |
| 857  | ALFORD | MARK  | B    | 5.00 | H    |  NULL |
+------+--------+-------+------+------+------+-------+

I used MySQL for the examples in this article. For information on getting started with MySQL, see our January 2004 article.

Want to know more about SQL? Give us a call! You can count on Caliber Data Training for top quality mainframe and/or internet programming education.