RIGHT, FULL, and CROSS JOIN in DB2 SQL

Join types at a glance

A join matches rows of one table reference with rows of another using a join condition (except CROSS JOIN). IBM describes an outer join as a method of combining tables so the result includes unmatched rows of one table, or of both. The matching is based on the join condition in ON.

Vocabulary that prevents mix-ups:

• Preserved side — the table whose unmatched rows still appear (left for LEFT, right for RIGHT, both for FULL).
• Null-supplying side — the table that contributes a null-extended row when there is no match.
• Paired rows — rows for which the join condition is TRUE (the inner-join part).

RIGHT OUTER JOIN

A right outer join result includes the rows from the right table that were missing from the inner join. The result of T1 RIGHT OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T2, the concatenation of that row with the null row of T1. All columns derived from T1 allow null values in those extra rows.

The word OUTER is optional: RIGHT JOIN means the same as RIGHT OUTER JOIN. You still need ON with a join condition.

1
2
3
4
5
6
7
8
SELECT E.EMPNO,
       E.LASTNAME,
       D.DEPTNO,
       D.DEPTNAME
  FROM DSN8C10.EMP E
  RIGHT OUTER JOIN DSN8C10.DEPT D
    ON E.WORKDEPT = D.DEPTNO
 ORDER BY D.DEPTNO, E.EMPNO;

Read this as: “start from DEPT (the right table). Keep every department. Attach matching employees when WORKDEPT equals DEPTNO. If a department has no employees, employee columns are null.” That is the usual business question people accidentally write as a right join when they listed EMP first.

Rewrite RIGHT as LEFT

The following is equivalent and is what many Db2 shops standardize on. Same preserved table (DEPT), different spelling:

1
2
3
4
5
6
7
8
SELECT E.EMPNO,
       E.LASTNAME,
       D.DEPTNO,
       D.DEPTNAME
  FROM DSN8C10.DEPT D
  LEFT OUTER JOIN DSN8C10.EMP E
    ON E.WORKDEPT = D.DEPTNO
 ORDER BY D.DEPTNO, E.EMPNO;

Internally, Db2 converts every right outer join to a left outer join at execution. PLAN_TABLE column JOIN_TYPE uses blank for inner (or no join), L for left outer, and F for full outer. You will not see a dedicated “R” for right. That is why EXPLAIN of a RIGHT JOIN looks like a LEFT JOIN—because it became one.

Prefer writing LEFT JOIN in new SQL so the preserved table is always the first table you read. Use RIGHT JOIN when generated SQL or a tool emits it, or when swapping table order would make a long FROM clause harder to follow.

FULL OUTER JOIN

A full outer join result includes the rows from both tables that were missing from the inner join. The result of T1 FULL OUTER JOIN T2 consists of their paired rows, plus unmatched T1 rows concatenated with a null T2 row, plus unmatched T2 rows concatenated with a null T1 row. All columns of the result table allow null values.

1
2
3
4
5
6
7
8
SELECT COALESCE(E.WORKDEPT, D.DEPTNO) AS DEPTNO,
       E.EMPNO,
       E.LASTNAME,
       D.DEPTNAME
  FROM DSN8C10.EMP E
  FULL OUTER JOIN DSN8C10.DEPT D
    ON E.WORKDEPT = D.DEPTNO
 ORDER BY 1, E.EMPNO;

Why COALESCE on the department number? On a match, both keys are equal. On an unmatched employee, D.DEPTNO is null and E.WORKDEPT holds the orphan code. On an unmatched department, E.WORKDEPT is null and D.DEPTNO holds the empty department. A report that SELECTs only E.WORKDEPT silently blanks out empty departments; selecting only D.DEPTNO blanks out employees with a bad WORKDEPT. COALESCE (or CASE) builds a single display key.

When FULL OUTER JOIN is the right tool

• Reconciliation — file A versus file B: matches, left-only, right-only in one result.
• Referential cleanup — find child rows whose parent key does not exist and parent keys with no children, together.
• Merged dimensions — two independently maintained code tables that should represent the same domain.

FULL OUTER JOIN is often more expensive than INNER or LEFT because the engine must preserve unmatched rows from both inputs. Do not use it “just in case.” If you only need unmatched parents, LEFT JOIN is enough. If you only need unmatched children, swap to LEFT JOIN from the child or use NOT EXISTS.

You can also express a full outer join as a UNION of a left join and a right join that keeps only unmatched right rows, but native FULL OUTER JOIN is clearer and lets the optimizer see one join.

ON versus WHERE (outer joins)

This trap is the same for LEFT, RIGHT, and FULL. Predicates in ON decide which pairs match. Predicates in WHERE filter the join result afterward. If you put a filter on the null-supplying side in WHERE, unmatched rows disappear because those columns are null.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Keeps departments with no employees (EMP columns null)
SELECT D.DEPTNO, E.EMPNO
  FROM DSN8C10.DEPT D
  LEFT JOIN DSN8C10.EMP E
    ON E.WORKDEPT = D.DEPTNO
   AND E.JOB = 'MANAGER';
 
-- WHERE E.JOB = 'MANAGER' drops departments with no matching manager
-- because E.JOB is null on unmatched rows — behaves like an inner join
SELECT D.DEPTNO, E.EMPNO
  FROM DSN8C10.DEPT D
  LEFT JOIN DSN8C10.EMP E
    ON E.WORKDEPT = D.DEPTNO
 WHERE E.JOB = 'MANAGER';

Filters that belong to the preserved table (for example D.LOCATION = 'DALLAS') usually belong in WHERE: you want to drop whole preserved rows. Filters that describe “which matching rows from the optional table” belong in ON so unmatched preserved rows survive.

CROSS JOIN

A CROSS JOIN is the Cartesian product: each row of the left table combined with every row of the right table. There is no ON clause. If T1 has m rows and T2 has n rows, the result has m × n rows.

1
2
3
SELECT D.DEPTNO, P.PROJNO
  FROM DSN8C10.DEPT D
  CROSS JOIN DSN8C10.PROJ P;

Legitimate uses are uncommon but real:

• Generate combinations — every department × every product line for a planning grid, then LEFT JOIN actuals.
• Attach a one-row set — CROSS JOIN a single-row VALUES table or dummy table of report parameters (as-of date, run id) onto a detail result.
• Calendar explosion — every account × every month in a reporting period (watch the size).

Accidental Cartesian products are a classic outage: two 10,000-row tables become 100 million rows, work-file explosions, and a utility timeout. Old comma FROM lists without a WHERE join predicate do the same thing. Write CROSS JOIN only when you mean every combination. Write INNER JOIN … ON when you mean matched keys.

1
2
3
4
-- Intentional: one parameter row attached to every employee
SELECT E.EMPNO, E.LASTNAME, P.ASOF_DATE
  FROM DSN8C10.EMP E
  CROSS JOIN (VALUES (CURRENT DATE)) AS P(ASOF_DATE);

CROSS JOIN is not an outer join. It does not null-pad. If either table is empty, the product is empty. That surprises people who expected “keep the left side anyway”—that requirement is LEFT JOIN, not CROSS JOIN.

Multiple joins and nesting

You can nest joined tables with parentheses. Mixing FULL OUTER JOIN with later inner joins needs care: an inner join after a full outer join can discard the unmatched rows you just preserved, unless you join only on COALESCE keys and choose outer joins all the way. Build two-table joins first, check row counts, then add the next table.

Correlation names (E, D, P) are not optional style—they are how you qualify EMPNO versus DEPTNO when both tables have similarly named columns. After an outer join, qualifying matters even more because the “same” business key may live in two nullable columns.

Explain It Like I'm Five

Imagine a list of children and a list of lunchboxes. Inner join keeps only children who have a lunchbox and lunchboxes that have a child. Left join keeps every child; missing lunchboxes show up as empty. Right join keeps every lunchbox; missing children show up as empty. Full join keeps every child and every lunchbox, empty on whichever side is missing. Cross join makes every child try every lunchbox, even if that is 20 children times 20 boxes—usually a mess, sometimes a seating chart you actually wanted.

Exercises

1. Rewrite a RIGHT OUTER JOIN from EMP to DEPT as a LEFT OUTER JOIN that returns the same rows.
2. Write a FULL OUTER JOIN of EMP and DEPT and explain why COALESCE is used on the department key in the SELECT list.
3. Predict the row count of DEPT CROSS JOIN PROJ if DEPT has 9 rows and PROJ has 6 rows.
4. Take a LEFT JOIN and move a predicate from ON to WHERE on the right table. Explain which unmatched rows vanish.
5. Look at EXPLAIN output for a RIGHT JOIN and note the JOIN_TYPE value. Why is it not R?

Frequently asked questions

Is RIGHT JOIN slower than LEFT JOIN?

Not in itself—Db2 rewrites RIGHT to LEFT. Performance follows table size, join predicates, indexes, and whether you preserved a large unmatched set. FULL OUTER JOIN is the one that often costs more than LEFT or INNER.

Can I CROSS JOIN then filter with WHERE instead of INNER JOIN?

Functionally a CROSS JOIN plus WHERE a.key = b.key can resemble an inner join, but you should write INNER JOIN … ON. The optimizer and the next reader both understand ON as the join. WHERE is for residual filters.

Do unmatched outer-join rows lock the null-supplying table?

Unmatched preserved rows did not find a partner; locking and isolation still follow the access path Db2 chose. Outer joins do not magically skip locks on the tables you named. Isolation clauses and SKIP LOCKED DATA are separate topics.