INNER JOIN in DB2 for z/OS

JOIN and INNER JOIN

A join operation typically matches a row of one table with a row of another on a join condition. The result of T1 INNER JOIN T2 is their paired rows. If a join operator is not specified, INNER is the default. That is why people say “join” when they mean “inner join,” and why FROM T1 JOIN T2 ON … is an inner join unless you write LEFT, RIGHT, FULL, or CROSS.

Inner join means discard. A part whose product number is not in PRODUCTS does not appear. A product with no parts listed does not appear. Outer joins exist specifically to keep those orphans; the next page covers LEFT OUTER JOIN.

The join condition can be any simple or compound search condition that does not contain a subquery. Equality of key columns is the usual case (equijoin). You can also write non-equal comparisons, AND extra local filters into ON, or even ON 1=1 to force every combination—the same Cartesian product you get from a comma join with no WHERE.

JOIN ON

When you name the join in FROM with INNER JOIN (or JOIN), you put the join condition in ON, not in WHERE. ON is required for that syntax.

1
2
3
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

IBM’s sample PARTS / PRODUCTS data yields only matching product numbers: WIRE and MAGNETS with GENERATOR (10), PLASTIC with RELAY (30), BLADES with SAW (205). OIL (product 160, not in PRODUCTS) and SCREWDRIVER (product 505, no parts) are gone.

You may AND extra predicates onto ON. They still participate in the inner-join match. For an inner join, IBM notes that ON predicates can supply both the join condition and local filtering and are semantically equivalent to WHERE predicates. Putting the key match in ON and the business filter in WHERE is still clearer:

1
2
3
4
5
6
7
8
9
10
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#
 WHERE SUPPLIER NOT LIKE 'A%';
 
-- Same rows for an inner join if the extra predicate is in ON instead
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#
   AND SUPPLIER NOT LIKE 'A%';

Qualify column names whenever both tables have the same name (PROD#). Correlation names keep SQL readable:

1
2
3
4
5
SELECT E.EMPNO, E.LASTNAME, D.DEPTNAME
  FROM HR.EMPLOYEE AS E
  INNER JOIN HR.DEPARTMENT AS D
    ON E.WORKDEPT = D.DEPTNO
 WHERE D.LOCATION = 'DALLAS';

INNER is optional in the keyword sequence: INNER JOIN and JOIN mean the same inner join. Write INNER JOIN when the team wants the type visible next to LEFT OUTER JOIN in the same statement.

Comma joins (implicit INNER JOIN)

Listing tables in FROM separated by commas is an implicit inner join. The join condition belongs in WHERE. If you forget WHERE, you get every combination of rows: the product of the table sizes.

1
2
3
4
5
6
7
8
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS, PRODUCTS
 WHERE PARTS.PROD# = PRODUCTS.PROD#;
 
-- Cartesian product (same idea as ON 1=1)
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON 1 = 1;

Self-joins use the same idea. Two correlation names, one table, a relationship between rows:

1
2
3
SELECT A.PROJNO, A.PROJNAME, B.PROJNO, B.PROJNAME
  FROM DSN8C10.PROJ A, DSN8C10.PROJ B
 WHERE A.PROJNO = B.MAJPROJ;

A is the major project, B is a subproject whose MAJPROJ points at A. You can write the same self-join with INNER JOIN ON A.PROJNO = B.MAJPROJ. Prefer the explicit form in new code so the join is not mixed into a long WHERE of local filters.

JOIN USING (not the z/OS form)

ANSI SQL and some IBM products (Db2 LUW, Db2 for i) allow:

1
2
3
4
-- LUW / IBM i shorthand — not how you write z/OS inner joins
SELECT EMPNO, ACSTDATE
  FROM CORPDATA.PROJACT INNER JOIN CORPDATA.EMPPROJACT
  USING (PROJNO, ACTNO);

USING (PROJNO, ACTNO) means “join where those same-named columns are equal.” On those products SELECT * even coalesces the join columns so they appear once. Db2 for z/OS application programming documents inner joins with ON or with a comma and WHERE. The z/OS SQL Reference’s USING keyword in other statements is the USING clause of EXECUTE and OPEN (parameter lists), not a join shorthand.

On z/OS, write the equivalent ON predicate explicitly:

1
2
3
4
5
6
SELECT EMPNO, ACSTDATE
  FROM PROJACT AS P
  INNER JOIN EMPPROJACT AS E
    ON P.PROJNO = E.PROJNO
   AND P.ACTNO = E.ACTNO
 WHERE ACSTDATE > DATE('1982-12-31');

If you copy USING from a LUW blog into a z/OS program, expect a syntax error. Teach the idea—same-name equijoin—then always expand it to ON on this platform. NATURAL JOIN is likewise not the z/OS inner-join style; name the columns.

Matching rules beginners miss

• Null keys do not match. NULL = NULL is UNKNOWN, so inner join does not pair two nulls.
• One-to-many duplicates rows. One department with 40 employees yields 40 result rows. That is correct, not a bug.
• Many-to-many explodes. If both sides have duplicate keys, you get the product of matches. Check uniqueness of join columns when counts look huge.
• Type and CCSID. Join columns must be comparable. CHAR versus VARCHAR and mixed encodings can drop matches you expected.
• ON cannot use a subquery as the join condition. Filter with WHERE, or join to a nested table expression instead.

Multiple inner joins chain in FROM: A JOIN B ON … JOIN C ON …. Each ON refers to tables already in scope. Join order in the text is not necessarily the optimizer’s join sequence; EXPLAIN shows that. Write the statement so humans can see each relationship.

Explain It Like I'm Five

You have a box of kids’ name tags and a box of lunch boxes labeled with names. An inner join is “only keep a name tag when there is a lunch box with the same name, and only keep a lunch box when there is a tag.” A tag with no lunch, or a lunch with no tag, stays in the box. ON is the instruction “same name.” USING on other databases is a shortcut that says “the column called NAME in both boxes”; on z/OS you write that shortcut out in full.

Exercises

1. Write an INNER JOIN of EMPLOYEE and DEPARTMENT on WORKDEPT = DEPTNO returning EMPNO, LASTNAME, and DEPTNAME.
2. Rewrite the same join as a comma FROM with the condition in WHERE. When would the explicit JOIN form be safer?
3. Using the PARTS/PRODUCTS story, list which sample rows an inner join drops and why.
4. Translate a LUW JOIN … USING (PROJNO, ACTNO) into z/OS ON predicates.
5. Write a self-join that lists each employee number together with their manager’s last name (assume MGRNO on DEPARTMENT or similar). Use two correlation names.