Joins on IBM i: INNER, LEFT, and EXCEPTION JOIN with Worked Examples

Who this is for: RPG and SQL developers who know the shape of a JOIN statement but want concrete, worked examples against realistic tables — including Db2 for i’s own EXCEPTION JOIN, which does not exist in the ANSI SQL standard and will not be in most SQL textbooks.

Verification level: Join syntax and behavior below are checked against IBM’s Db2 for i SQL programming documentation (see the References section near the end of this article). All examples are IBM DOC CHECKED / STATIC CHECKED — reviewed for syntactic correctness against the documented grammar and the EXCEPTION JOIN example IBM itself publishes, not compiled or executed on a live system.

The two sample tables

Every example below joins the same two small tables, based on IBM’s own documented example tables for joins (CORPDATA.EMPLOYEE and CORPDATA.PROJECT), adapted here with explicit sample rows so the result of each join is traceable by hand:

-- EMPNO is the key; MGRNO and the rest are omitted for brevity
CREATE TABLE APPLIB.EMPLOYEE (
    EMPNO     CHAR(6)      NOT NULL,
    LASTNAME  VARCHAR(15)  NOT NULL,
    WORKDEPT  CHAR(3)
);

CREATE TABLE APPLIB.PROJECT (
    PROJNO    CHAR(6)      NOT NULL,
    PROJNAME  VARCHAR(24)  NOT NULL,
    RESPEMP   CHAR(6)      NOT NULL
);

-- Sample rows
-- EMPLOYEE: ('000010','HAAS','A00'), ('000020','THOMPSON','B01'),
--           ('000030','KWAN','C01'),  ('000050','GEYER','E01')
-- PROJECT:  ('AD3100','ADMIN SERVICES','000010'),
--           ('IF1000','QUERY SERVICES','000030'),
--           ('MA2100','WELD LINE AUTOMATION','000010')

Only employees 000010 and 000030 are responsible for a project; 000020 and 000050 are not.

INNER JOIN — only the matches

Db2 for i’s documented definition, confirmed against IBM’s join programming topic: “An inner join returns only the rows from each table that have matching values in the join columns.”

SELECT E.EMPNO, E.LASTNAME, P.PROJNO, P.PROJNAME
FROM   APPLIB.EMPLOYEE E
INNER JOIN APPLIB.PROJECT P
   ON E.EMPNO = P.RESPEMP
ORDER BY E.EMPNO, P.PROJNO;

Worked result against the sample rows:

Three rows. 000020 and 000050 do not appear anywhere — they have no matching PROJECT row, and an inner join never manufactures one. HAAS appears twice because she is the responsible employee on two separate projects; that is correct, expected inner-join behavior, not a bug, but see “Common mistakes” below for how this same multiplication becomes a real bug when it happens unintentionally.

LEFT OUTER JOIN — matches plus unmatched left-side rows

IBM’s documented definition: “A left outer join returns all the rows that an inner join returns plus one row for each of the other rows in the first table” — the rows in the first (left) table that have no match in the second table, with nulls filled in for the second table’s columns.

SELECT E.EMPNO, E.LASTNAME, P.PROJNO, P.PROJNAME
FROM   APPLIB.EMPLOYEE E
LEFT OUTER JOIN APPLIB.PROJECT P
   ON E.EMPNO = P.RESPEMP
ORDER BY E.EMPNO, P.PROJNO;

Worked result:

Every employee now appears at least once. THOMPSON and GEYER show NULL in the PROJECT columns because they have no matching row — this is the standard way to answer “which employees have no project responsibility,” though EXCEPTION JOIN (below) answers that specific question more directly, without needing a WHERE ... IS NULL filter on the outer join’s result.

IBM’s documented right outer join is, per the same source, simply “the same as a left outer join with the tables specified in the opposite order” — A LEFT OUTER JOIN B and B RIGHT OUTER JOIN A return the same result set.

EXCEPTION JOIN — Db2 for i’s anti-join

EXCEPTION JOIN is specific to Db2 for i; it is not part of the ISO/IEC SQL standard, and other databases express the same query with NOT EXISTS or LEFT JOIN ... WHERE right.key IS NULL. IBM’s documented definition: “A left exception join returns only the rows from the first table that do not have a match in the second table.” A right exception join is documented as working “just like a left exception join but with the tables reversed.”

IBM’s own published example (from the same join-programming topic, reproduced here against the sample tables above):

SELECT EMPNO, LASTNAME, PROJNO
FROM   APPLIB.EMPLOYEE EXCEPTION JOIN APPLIB.PROJECT
   ON EMPNO = RESPEMP
WHERE LASTNAME > 'S';

Worked result against the sample rows: only 000050, GEYER, NULL — THOMPSON’s last name does not sort after 'S', so she is filtered out by the WHERE clause even though she also has no project; GEYER qualifies on both conditions. The PROJNO column is always null in an exception join’s result, confirmed by IBM’s own description of the example: the query “returns employees whose last names sort after ‘S’ and who are not responsible for any project,” and PROJNO is null for every returned row because, by definition, there is no matching PROJECT row to pull it from.

A simpler exception join with no extra filter, to directly answer “which employees have no project”:

SELECT EMPNO, LASTNAME
FROM   APPLIB.EMPLOYEE EXCEPTION JOIN APPLIB.PROJECT
   ON EMPNO = RESPEMP;

Worked result: 000020, THOMPSON and 000050, GEYER — the same two rows the LEFT OUTER JOIN ... WHERE P.PROJNO IS NULL pattern would produce, but without needing the WHERE ... IS NULL filter, and without ever constructing the matched rows in the first place.

Tooling caveat. Some SQL editors and linters (a JetBrains DataGrip community thread surfaced during research for this article is one example) flag EXCEPTION JOIN as a syntax error because it is outside the ANSI grammar they expect, even though Db2 for i executes it correctly. Do not treat an IDE’s red underline under EXCEPTION JOIN as proof the statement is wrong — check it against Db2 for i’s own SQL reference, or just run it.

Common join mistakes

Accidental cartesian products

Omitting the join condition, or getting it wrong so it never actually narrows the match, produces every combination of rows from both tables:

-- Mistake: no ON/WHERE condition relating the two tables
SELECT E.EMPNO, P.PROJNO
FROM   APPLIB.EMPLOYEE E, APPLIB.PROJECT P;

Against the four EMPLOYEE rows and three PROJECT rows in the sample data, this returns 12 rows (4 × 3) — not the 3 rows the inner join above returns. On small sample tables, a cartesian product is an obvious oddity; on real tables with tens of thousands of rows on each side, the same mistake can return many millions of rows, run for an uncomfortably long time, and in some client tools silently truncate the display, hiding the fact anything is wrong. The classic cause is writing a comma-separated FROM list (an implicit cross join) and forgetting the WHERE clause that was meant to relate the tables, or writing an explicit JOIN ... ON condition that references the wrong columns so it matches far more pairs than intended, such as joining on a non-unique status code instead of a key.

A second, less obvious cause: joining two tables with a condition that is logically always true for some rows, for example ON P.RESPEMP = P.RESPEMP typed by mistake instead of ON E.EMPNO = P.RESPEMP — syntactically valid, but matches every row of PROJECT against every row of EMPLOYEE because the condition does not actually relate the two tables. Always check that every table in the FROM/JOIN list is referenced in at least one join condition, and sanity-check the row count of the result against what you expect before trusting it.

NULL handling in join conditions

A join’s ON condition is itself a search condition, and search conditions follow three-valued logic: TRUE, FALSE, or UNKNOWN. A row pair is included only when the condition evaluates to TRUE — UNKNOWN is treated the same as FALSE for join-matching purposes. This causes two common surprises:

• A null in the join column never matches anything, including another null. NULL = NULL evaluates to unknown, not true, so a row whose join column is null will never satisfy a plain = join condition against any row on the other side — in an inner join it simply vanishes from the result, and in a left outer join it survives as one of the “unmatched” rows with nulls filled in, which is often exactly what you want, but can be mistaken for a bug if you expected two nulls to “match.”
• A WHERE clause applied after an outer join can silently turn it back into an inner join. This is the single most common outer-join mistake:

-- Looks like a left outer join, but isn't one in effect
SELECT E.EMPNO, E.LASTNAME, P.PROJNO
FROM   APPLIB.EMPLOYEE E
LEFT OUTER JOIN APPLIB.PROJECT P
   ON E.EMPNO = P.RESPEMP
WHERE  P.PROJNAME = 'QUERY SERVICES';

The LEFT OUTER JOIN correctly produces a null-filled row for THOMPSON and GEYER, but the WHERE P.PROJNAME = 'QUERY SERVICES' clause then throws those rows away, because NULL = 'QUERY SERVICES' is unknown, not true. The net effect is indistinguishable from an inner join with an extra filter — the outer join did nothing. The fix is to move any condition on the outer (nullable) side’s columns into the ON clause (where it narrows which rows match, without discarding unmatched left rows), or to explicitly allow for the null case in the WHERE clause with (P.PROJNAME = 'QUERY SERVICES' OR P.PROJNAME IS NULL) if unmatched rows should still be kept.

Confusing EXCEPTION JOIN with “no match on either side”

EXCEPTION JOIN only answers “rows in the first table with no match in the second.” It does not, by itself, give you a full picture of unmatched rows on both sides (a full outer anti-join). If you need both directions, combine a left exception join and a right exception join with UNION ALL, following the same pattern IBM’s own documentation shows for building a full outer join out of a left outer join and a right exception join.

FAQ

Is EXCEPTION JOIN standard SQL?

No. It is specific to Db2 for i. The ISO/IEC SQL standard’s equivalent construct is NOT EXISTS (or, in most other SQL dialects, LEFT JOIN ... WHERE right.key IS NULL).

Does a LEFT OUTER JOIN with a WHERE clause on the right table still behave like an outer join?

Only if that WHERE clause explicitly tolerates the null case (for example, with OR column IS NULL). Otherwise, Db2 for i’s three-valued logic (UNKNOWN is treated as not-matching) will filter out the very “unmatched” rows the outer join was meant to preserve, making it behave like an inner join.

Why does a row disappear from an inner join if its join column is null?

Because NULL = NULL and NULL = anything both evaluate to unknown rather than true, and a join’s ON condition only keeps rows where the condition is true.

What is the fastest way to find rows in Table A with no match in Table B on IBM i?

A EXCEPTION JOIN B ON ... is the documented IBM i idiom for this and is generally the clearest way to express it; NOT EXISTS and LEFT JOIN ... WHERE B.key IS NULL are portable alternatives that produce the same logical result.

References

• Db2 for i SQL programming: joining data from more than one table — the inner join definition (“returns only the rows from each table that have matching values in the join columns”); the left outer join definition; that a right outer join “is the same as a left outer join with the tables specified in the opposite order”; the left exception join definition; and the CORPDATA.EMPLOYEE/CORPDATA.PROJECT example tables this article adapts.
• Db2 for i SQL programming: the exception join topic — the left exception join definition, IBM’s own worked CORPDATA.EMPLOYEE EXCEPTION JOIN CORPDATA.PROJECT example and its described result, and the note that “there is a right exception join, too, that works just like a left exception join but with the tables reversed.”
• Db2 for i SQL programming: the inner join topic — the inner join description and its equivalent JOIN and WHERE-clause forms.

Not independently confirmed: EXCEPTION JOIN behavior against remote or federated tables, and whether IDEs other than one community report (which flags EXCEPTION JOIN as invalid even though it executes correctly on Db2 for i) show the same false warning.

Internal links

• Db2 for IBM i Explained
• DB2 for i CTEs and Recursive SQL
• Db2 for i Query Optimization: Visual Explain, SQE vs CQE, Indexes, STRDBMON, Plan Cache