LEFT OUTER JOIN in DB2 SQL

Preserved versus null-supplying side

Outer-join vocabulary is easier if you memorize two roles:

IBM’s definition: the result of T1 LEFT OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2. All columns derived from T2 allow null values in that result, even if T2’s base columns were defined NOT NULL. The join invented nulls; they are not stored in T2.

Unmatched T2 rows do not appear. If you needed those instead, that is a RIGHT OUTER JOIN (T2 preserved) or you swap the table order and keep writing LEFT. Full outer join keeps unmatched rows from both sides. Cross join keeps every combination and is not an outer join.

One-to-many matches still duplicate the left row. A department with three employees yields three result rows. A department with zero employees yields one row: the department columns filled in, employee columns null. That single null-padded row is the whole point of a left join from DEPARTMENT to EMPLOYEE.

LEFT JOIN syntax

Write the join in FROM. The OUTER keyword is optional: LEFT JOIN and LEFT OUTER JOIN are the same. You must use ON for the join condition (not a comma-only FROM). For outer joins other than full outer join, ON may use any predicates except predicates that contain subqueries.

1
2
3
4
5
SELECT E.EMPNO, E.LASTNAME, D.DEPTNAME, D.DEPTNO
  FROM HR.DEPARTMENT AS D
  LEFT OUTER JOIN HR.EMPLOYEE AS E
    ON E.WORKDEPT = D.DEPTNO
 ORDER BY D.DEPTNO, E.EMPNO;

Every department appears. Departments with staff show one row per employee. Departments with no staff show one row with EMPNO and LASTNAME null. If you had written INNER JOIN, empty departments would vanish.

1
2
3
4
5
-- IBM-style parts example: keep products even when no parts match
SELECT PRODUCTS.PROD#, PRODUCT, PART, SUPPLIER
  FROM PRODUCTS
  LEFT JOIN PARTS
    ON PRODUCTS.PROD# = PARTS.PROD#;

SCREWDRIVER (505) survives with null PART and SUPPLIER. OIL still does not appear if PRODUCTS is on the left and 160 is not a product row—the preserved side is PRODUCTS, not PARTS. Choose the left table according to whose leftovers you must keep.

COALESCE is the usual way to display a friendly value for null-supplied columns:

1
2
3
4
5
6
SELECT D.DEPTNO,
       D.DEPTNAME,
       COALESCE(E.LASTNAME, '(no employees)') AS LASTNAME
  FROM HR.DEPARTMENT AS D
  LEFT OUTER JOIN HR.EMPLOYEE AS E
    ON E.WORKDEPT = D.DEPTNO;

Nested table expressions can sit on either side. As the right operand of a left join, unmatched rows from the nested result are not preserved—only unmatched rows from the left operand are. As the left operand, unmatched rows of the expression are preserved and the right table is null-supplied. Correlation names on the expression (AS TEMP, AS PARTX) are required so you can name its columns in ON and SELECT.

WHERE versus ON filter differences

For inner joins, ON and WHERE are often interchangeable. For left outer joins they are not. ON is evaluated as part of matching. WHERE is evaluated on the result after null-padding. A filter on a null-supplying column in WHERE is UNKNOWN for unmatched left rows, and WHERE keeps only TRUE.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Keeps Dallas departments even if they have no employees
SELECT D.DEPTNO, D.DEPTNAME, E.EMPNO
  FROM HR.DEPARTMENT AS D
  LEFT OUTER JOIN HR.EMPLOYEE AS E
    ON E.WORKDEPT = D.DEPTNO
 WHERE D.LOCATION = 'DALLAS';
 
-- Looks similar but DROPS departments that have no clerk
-- (unmatched rows have JOB null, so JOB = 'CLERK' is UNKNOWN)
SELECT D.DEPTNO, D.DEPTNAME, E.EMPNO
  FROM HR.DEPARTMENT AS D
  LEFT OUTER JOIN HR.EMPLOYEE AS E
    ON E.WORKDEPT = D.DEPTNO
 WHERE E.JOB = 'CLERK';
 
-- Same clerk filter without killing empty departments: put it in ON
SELECT D.DEPTNO, D.DEPTNAME, E.EMPNO
  FROM HR.DEPARTMENT AS D
  LEFT OUTER JOIN HR.EMPLOYEE AS E
    ON E.WORKDEPT = D.DEPTNO
   AND E.JOB = 'CLERK';

Read the third form carefully. ON … AND E.JOB = 'CLERK' means “attach only clerk employees.” Non-clerk employees do not match, so those department rows still appear, with null employee columns—just like a department that had no clerks. That is usually what “departments and their clerks, if any” means. The WHERE form means “departments that have at least one clerk,” which is an inner join in disguise.

The legitimate WHERE-on-the-right-side pattern is the orphan query:

1
2
3
4
5
6
-- Departments with no employees
SELECT D.DEPTNO, D.DEPTNAME
  FROM HR.DEPARTMENT AS D
  LEFT OUTER JOIN HR.EMPLOYEE AS E
    ON E.WORKDEPT = D.DEPTNO
 WHERE E.EMPNO IS NULL;

Here UNKNOWN is not the problem: you asked for IS NULL, which is TRUE on the padded rows and FALSE on matched rows. That is how you find missing children, missing dimensions, or keys that failed a load.

Another safe pattern is to filter the right table before the join with a nested table expression. Predicates inside the nested SELECT run as local (often stage 1) filters on T2, then the left join pads. IBM uses this when an ON clause would otherwise be too late, especially around full outer joins; it is just as useful for left joins when the right-side filter is complicated.

1
2
3
4
5
6
7
8
SELECT D.DEPTNO, D.DEPTNAME, C.EMPNO, C.LASTNAME
  FROM HR.DEPARTMENT AS D
  LEFT OUTER JOIN (
        SELECT EMPNO, LASTNAME, WORKDEPT
          FROM HR.EMPLOYEE
         WHERE JOB = 'CLERK'
       ) AS C
    ON C.WORKDEPT = D.DEPTNO;

Common beginner mistakes

• Filtering the right table in WHERE and wondering where the unmatched left rows went. Move the predicate to ON, or use IS NULL if you want orphans.
• Wrong preserved table. “Show all parts, even without a product” needs PARTS on the left. Putting PRODUCTS on the left keeps leftover products instead.
• Assuming NOT NULL columns cannot be null in the result. Outer-join padding overrides that for the null-supplying side. Host variables need indicators.
• Counting with COUNT(E.EMPNO) versus COUNT(*). COUNT(*) counts padded rows (empty departments count as 1). COUNT(E.EMPNO) skips nulls, so empty departments contribute 0 employees—usually what you want in a headcount.
• Joining on columns that can be null and expecting nulls to match. They do not. Unmatched is the outcome.
• Copying USING from LUW. Write ON left.col = right.col on z/OS.
• Forgetting that one-to-many duplicates the left row when aggregating. GROUP BY the left key or pre-aggregate the right side.

The order of successive outer joins can change the result. (A LEFT JOIN B) LEFT JOIN C is not always the same as A LEFT JOIN (B LEFT JOIN C). Parenthesize joined-tables when the shape is not a simple chain, and test with small known data.

Explain It Like I'm Five

The left box is every kid in the class. The right box is lunch boxes. A left join lines kids up and, when a lunch has that kid’s name, hands it over. A kid with no lunch still stands in line holding an empty tray (nulls). A spare lunch with no kid is put away. If you then say “only keep people whose lunch is a sandwich,” kids with empty trays fail the sandwich test and leave—so you accidentally sent home the kids you meant to keep. Say “hand them a sandwich lunch if there is one” (ON) instead of “after lining up, throw out anyone without a sandwich” (WHERE).

Exercises

1. Write a left join from DEPARTMENT to EMPLOYEE. Predict the result row for a department that has no employees.
2. Take that query and add WHERE E.JOB = 'CLERK'. Then move the job test into ON. Explain the two result sets.
3. Write a query that lists products with no matching parts using LEFT JOIN and IS NULL.
4. Why might COUNT(*) and COUNT(E.EMPNO) disagree after a left join from departments to employees?
5. Draw preserved versus null-supplying sides for EMP LEFT JOIN DEPT on WORKDEPT = DEPTNO. Which unmatched rows survive?