DB2 writing joins

Prerequisites and the example schema

You should already be comfortable with SELECT, FROM, WHERE, column aliases, primary keys, and foreign keys. A primary key identifies one row. A foreign key records a relationship to a parent row, although DB2 can join columns even when no foreign-key constraint exists. A constraint protects data integrity; the join predicate tells one query how rows should be combined.

The examples use departments, employees, projects, and assignments. One department can have many employees. One employee can manage other employees, so MANAGER_ID points back to EMPLOYEE. Employees and projects have a many-to-many relationship represented by ASSIGNMENT. Its composite primary key prevents the same employee-project pair from being stored twice.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE DEPARTMENT (
  DEPT_ID    INTEGER NOT NULL PRIMARY KEY,
  DEPT_NAME  VARCHAR(60) NOT NULL
);
 
CREATE TABLE EMPLOYEE (
  EMP_ID      INTEGER NOT NULL PRIMARY KEY,
  EMP_NAME    VARCHAR(80) NOT NULL,
  DEPT_ID     INTEGER,
  MANAGER_ID  INTEGER,
  STATUS      CHAR(1) NOT NULL,
  FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT (DEPT_ID),
  FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE (EMP_ID)
);
 
CREATE TABLE PROJECT (
  PROJECT_ID    INTEGER NOT NULL PRIMARY KEY,
  PROJECT_NAME  VARCHAR(80) NOT NULL
);
 
CREATE TABLE ASSIGNMENT (
  EMP_ID       INTEGER NOT NULL,
  PROJECT_ID   INTEGER NOT NULL,
  HOURS        DECIMAL(7,2) NOT NULL,
  PRIMARY KEY (EMP_ID, PROJECT_ID),
  FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE (EMP_ID),
  FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT (PROJECT_ID)
);

Cardinality comes before syntax

Cardinality describes how many rows can participate on each side of a relationship: one-to-one, one-to-many, or many-to-many. Before writing SQL, state the expected grain of the output. “One row per employee” is different from “one row per employee-project assignment.” If an employee has three assignments, joining EMPLOYEE to ASSIGNMENT correctly produces three rows for that employee. Those rows are not SQL duplicates; they represent three relationship instances.

• One department to many employees: DEPARTMENT.DEPT_ID is unique, while EMPLOYEE.DEPT_ID can repeat.
• One manager to many direct reports: both roles come from EMPLOYEE, but MANAGER_ID can repeat.
• Many employees to many projects: ASSIGNMENT resolves the relationship into two one-to-many joins.

Estimate the plausible row count before execution. An inner join from employees to departments cannot produce more than one department match per employee when DEPT_ID is a valid unique parent key. A join that exceeds the employee count indicates either non-unique parent data, an incomplete predicate, or a different relationship than you assumed.

ANSI INNER JOIN

INNER JOIN returns only combinations for which the ON predicate is true. Employees with a null DEPT_ID or an unmatched department are excluded. Qualify columns with short, meaningful aliases so readers can see their source and DB2 does not report an ambiguous column reference.

1
2
3
4
5
6
7
SELECT E.EMP_ID,
       E.EMP_NAME,
       D.DEPT_NAME
FROM EMPLOYEE AS E
INNER JOIN DEPARTMENT AS D
  ON D.DEPT_ID = E.DEPT_ID
ORDER BY E.EMP_ID;

The keyword INNER is optional, but the ON predicate is essential. Prefer this explicit ANSI form to the older comma-separated FROM list with a relationship in WHERE. ANSI syntax keeps each relationship beside its joined table, makes outer joins possible, and makes a missing predicate easier to detect.

LEFT and RIGHT OUTER JOIN

LEFT OUTER JOIN preserves every row from its left input. If no right row satisfies ON, DB2 emits one result row and supplies null for every right-side column. This is useful for finding optional or missing relationships, such as departments that currently have no employees.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT D.DEPT_ID,
       D.DEPT_NAME,
       E.EMP_ID,
       E.EMP_NAME
FROM DEPARTMENT AS D
LEFT OUTER JOIN EMPLOYEE AS E
  ON E.DEPT_ID = D.DEPT_ID
ORDER BY D.DEPT_ID, E.EMP_ID;
 
-- Keep only departments with no employee match
SELECT D.DEPT_ID,
       D.DEPT_NAME
FROM DEPARTMENT AS D
LEFT JOIN EMPLOYEE AS E
  ON E.DEPT_ID = D.DEPT_ID
WHERE E.EMP_ID IS NULL;

RIGHT OUTER JOIN preserves every row from the right input. The next statement is logically equivalent to the first LEFT JOIN above because the inputs are reversed. RIGHT JOIN is valid ANSI SQL and supported by DB2, but many teams standardize on LEFT JOIN so the preserved table is consistently read first.

1
2
3
4
5
6
7
8
SELECT D.DEPT_ID,
       D.DEPT_NAME,
       E.EMP_ID,
       E.EMP_NAME
FROM EMPLOYEE AS E
RIGHT OUTER JOIN DEPARTMENT AS D
  ON E.DEPT_ID = D.DEPT_ID
ORDER BY D.DEPT_ID, E.EMP_ID;

FULL OUTER JOIN

FULL OUTER JOIN preserves unmatched rows from both inputs and also returns matching combinations. It is useful for reconciliation: compare a source extract with a target table and identify source-only, target-only, and matched keys in one result. Columns from either side may be null, so COALESCE can produce a common display key.

1
2
3
4
5
6
7
8
9
10
11
SELECT COALESCE(S.EMP_ID, T.EMP_ID) AS EMP_ID,
       CASE
         WHEN S.EMP_ID IS NULL THEN 'TARGET ONLY'
         WHEN T.EMP_ID IS NULL THEN 'SOURCE ONLY'
         ELSE 'MATCHED'
       END AS MATCH_STATUS,
       S.EMP_NAME AS SOURCE_NAME,
       T.EMP_NAME AS TARGET_NAME
FROM SOURCE_EMPLOYEE AS S
FULL OUTER JOIN TARGET_EMPLOYEE AS T
  ON T.EMP_ID = S.EMP_ID;

FULL OUTER JOIN can return more than one row per key if either input contains duplicate keys. Reconciliation queries should first establish whether the compared key is unique. Otherwise, two source rows and three target rows for one key produce six matched combinations.

CROSS JOIN and Cartesian products

CROSS JOIN deliberately pairs every row from one input with every row from the other. If a table of 7 days is crossed with 3 shifts, the result contains 21 day-shift combinations. This is useful for generating schedules, test combinations, or a complete reporting grid before left joining actual facts.

1
2
3
4
5
SELECT D.WORK_DATE,
       S.SHIFT_CODE
FROM REPORT_DATE AS D
CROSS JOIN SHIFT AS S
ORDER BY D.WORK_DATE, S.SHIFT_CODE;

An accidental Cartesian product has the same multiplication but lacks intent. It occurs when a JOIN has no relationship predicate or when old-style comma syntax omits a WHERE condition. With 10,000 employees and 5,000 projects, the intermediate result can reach 50 million rows before later filters. Use CROSS JOIN when all pairs are required. In every other case, review ON and compare actual counts with the expected cardinality.

Self joins

A self join gives two roles to the same table. EMPLOYEE is referenced once as the worker and once as the manager. A LEFT JOIN keeps executives and employees whose MANAGER_ID is null; an INNER JOIN would remove them.

1
2
3
4
5
6
7
8
SELECT E.EMP_ID,
       E.EMP_NAME,
       M.EMP_ID   AS MANAGER_ID,
       M.EMP_NAME AS MANAGER_NAME
FROM EMPLOYEE AS E
LEFT JOIN EMPLOYEE AS M
  ON M.EMP_ID = E.MANAGER_ID
ORDER BY E.EMP_ID;

Aliases are mandatory for human clarity and usually necessary for unambiguous SQL. A self join handles one relationship level. To traverse an unknown number of management levels, use a recursive common table expression rather than adding an arbitrary chain of manager joins.

Join predicates and composite relationships

A join predicate should express the complete logical relationship. Equality joins are most common, but range and inequality joins are valid when the model requires them. If a key consists of COMPANY_ID and EMP_ID, joining on EMP_ID alone can connect employees from different companies. Include every key component and use compatible data types.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT A.COMPANY_ID,
       A.EMP_ID,
       P.PAY_PERIOD,
       P.GROSS_PAY
FROM EMPLOYEE_ACCOUNT AS A
JOIN PAYROLL AS P
  ON P.COMPANY_ID = A.COMPANY_ID
 AND P.EMP_ID     = A.EMP_ID;
 
-- A non-equality range join
SELECT E.EMP_ID,
       E.SALARY,
       B.BAND_NAME
FROM EMPLOYEE_PAY AS E
JOIN SALARY_BAND AS B
  ON E.SALARY >= B.MIN_SALARY
 AND E.SALARY <  B.MAX_SALARY;

Range definitions must not overlap unless multiple matches are intentional. Avoid wrapping indexed join columns in functions or forcing DB2 to convert unlike data types. For example, joining INTEGER to CHAR with CAST can obscure a data-quality problem and prevent efficient matching. Align schema types when possible.

ON versus WHERE and null behavior

For an inner join, moving a simple filter between ON and WHERE often produces the same rows. For an outer join, placement changes meaning. ON determines whether a right-side row matches. WHERE filters the completed result after unmatched left rows have received null right-side values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Preserve every department; match only active employees
SELECT D.DEPT_NAME,
       E.EMP_NAME
FROM DEPARTMENT AS D
LEFT JOIN EMPLOYEE AS E
  ON E.DEPT_ID = D.DEPT_ID
 AND E.STATUS = 'A';
 
-- Removes departments without an active employee match
SELECT D.DEPT_NAME,
       E.EMP_NAME
FROM DEPARTMENT AS D
LEFT JOIN EMPLOYEE AS E
  ON E.DEPT_ID = D.DEPT_ID
WHERE E.STATUS = 'A';

In the second query, an unmatched row has E.STATUS equal to null. SQL uses three-valued logic: comparisons can be true, false, or unknown. NULL = 'A' is unknown, and WHERE retains only true rows. Adding OR E.STATUS IS NULL can sometimes preserve unmatched rows, but it is not identical in every data scenario to putting the qualification in ON. Begin with the business question, then place the predicate deliberately.

Many-to-many joins and duplicate-looking results

To list employees and their projects, join through ASSIGNMENT. The output grain is one row per assignment, not one row per employee. Selecting only employee names can make legitimate rows look duplicated because PROJECT_ID and PROJECT_NAME are hidden.

1
2
3
4
5
6
7
8
9
10
11
SELECT E.EMP_ID,
       E.EMP_NAME,
       P.PROJECT_ID,
       P.PROJECT_NAME,
       A.HOURS
FROM EMPLOYEE AS E
JOIN ASSIGNMENT AS A
  ON A.EMP_ID = E.EMP_ID
JOIN PROJECT AS P
  ON P.PROJECT_ID = A.PROJECT_ID
ORDER BY E.EMP_ID, P.PROJECT_ID;

Do not add DISTINCT as the first response to unexpected repetition. DISTINCT can hide an incomplete join while adding sort or hashing work. Diagnose whether the relationship is one-to-many, whether both sides contain duplicates, and whether a composite predicate is incomplete. Aggregate only when the requested grain is genuinely summarized, such as SUM(A.HOURS) GROUP BY E.EMP_ID, E.EMP_NAME.

Practical verification with counts

Test a join in stages. Record base table counts, count null and distinct relationship keys, then count the joined result. Group by the expected key to expose multiplication. For a LEFT JOIN, confirm that every left key remains. For an INNER JOIN, calculate unmatched keys separately instead of assuming foreign-key enforcement is active and all values are valid.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Establish input sizes
SELECT COUNT(*) AS EMPLOYEE_COUNT FROM EMPLOYEE;
SELECT COUNT(*) AS DEPARTMENT_COUNT FROM DEPARTMENT;
 
-- Check relationship values
SELECT COUNT(*)                  AS ROW_COUNT,
       COUNT(DEPT_ID)            AS NONNULL_DEPT_IDS,
       COUNT(DISTINCT DEPT_ID)   AS DISTINCT_DEPT_IDS
FROM EMPLOYEE;
 
-- Count the join before selecting presentation columns
SELECT COUNT(*) AS JOINED_COUNT
FROM EMPLOYEE AS E
JOIN DEPARTMENT AS D
  ON D.DEPT_ID = E.DEPT_ID;
 
-- Find keys that multiply unexpectedly
SELECT E.EMP_ID,
       COUNT(*) AS RESULT_ROWS
FROM EMPLOYEE AS E
JOIN ASSIGNMENT AS A
  ON A.EMP_ID = E.EMP_ID
GROUP BY E.EMP_ID
HAVING COUNT(*) > 1
ORDER BY RESULT_ROWS DESC;
 
-- Verify unmatched employee department references
SELECT COUNT(*) AS UNMATCHED_EMPLOYEES
FROM EMPLOYEE AS E
LEFT JOIN DEPARTMENT AS D
  ON D.DEPT_ID = E.DEPT_ID
WHERE E.DEPT_ID IS NOT NULL
  AND D.DEPT_ID IS NULL;

Use representative data, including null foreign keys, parents with no children, children with several matches, and boundary values for range joins. During diagnosis, select both join keys even if the final report does not display them. Comparing a few known business entities often reveals a faulty predicate faster than reading a large result.

Indexes, statistics, and EXPLAIN performance

Correctness comes first, but physical design determines how efficiently DB2 finds matches. Primary and unique keys normally have supporting unique indexes. Frequently joined foreign-key columns may also need indexes, especially when DB2 starts from the parent and probes many child rows. Composite index order should reflect useful join and filtering columns, not merely copy every predicate.

RUNSTATS supplies table cardinality, distinct values, distribution statistics, and index information to the optimizer. Stale statistics can make DB2 underestimate or overestimate join size, choose an unsuitable join sequence, or select a poor join method. Collect appropriate table-space and index statistics after major data changes and follow local production standards for detailed column-group or distribution statistics.

EXPLAIN records the optimizer's selected access path in plan tables. Review table access order, index use, matching columns, predicate stages, estimated cardinality, sort activity, and join method. Nested loop, merge scan, and hybrid join each fit different input sizes, ordering, and access patterns. Do not force a method merely because it was fast for another query; verify the access path and measure the workload with realistic parameter values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
EXPLAIN ALL SET QUERYNO = 410 FOR
SELECT E.EMP_ID,
       E.EMP_NAME,
       D.DEPT_NAME
FROM EMPLOYEE AS E
JOIN DEPARTMENT AS D
  ON D.DEPT_ID = E.DEPT_ID
WHERE E.STATUS = 'A';
 
-- Typical supporting indexes to evaluate, not automatic prescriptions
CREATE INDEX IX_EMPLOYEE_DEPT
  ON EMPLOYEE (DEPT_ID);
 
CREATE INDEX IX_EMPLOYEE_STATUS_DEPT
  ON EMPLOYEE (STATUS, DEPT_ID);

More indexes are not always better. Each index consumes storage and increases INSERT, UPDATE, DELETE, utility, and recovery work. Confirm that an index supports important access paths and check existing indexes before creating another. Expressions, casts, and data-type mismatches on join columns can reduce matching index use even when an index exists.

Common SQL errors and troubleshooting

Ambiguous and undefined columns

SQLCODE -203 commonly means a column name is ambiguous because more than one table reference provides it. Qualify the column with its correlation name. SQLCODE -206 means the referenced column is not valid in that context; check spelling, aliases, scope, and whether the column exists in the selected table.

Incompatible operands and invalid null assumptions

SQLCODE -401 indicates incompatible operand data types. Verify that joined columns have compatible numeric, character, date, or timestamp definitions instead of adding casual casts. To test null, use IS NULL or IS NOT NULL; equality with NULL never returns true. Remember that outer-join nulls can appear even when the base column is defined NOT NULL.

Unexpected counts and resource problems

A very high count usually points to a missing predicate, an omitted composite-key column, or a many-to-many relationship. SQLCODE -811 can occur when a SELECT INTO expected one row but the join returned several. Large accidental results can contribute to work-file pressure, timeouts, or resource-limit failures. Stop and validate the grain; do not patch the symptom with FETCH FIRST 1 ROW ONLY unless any arbitrary row truly meets the requirement.

A disciplined join-writing workflow

• State the output grain and expected relationship cardinality in plain language.
• Select the preserved input and choose INNER, LEFT, RIGHT, FULL, or CROSS intentionally.
• Write the complete key relationship in ON, including every composite-key column.
• Place matching qualifications in ON and final-result filters in WHERE deliberately.
• Select both key values during testing and compare base, joined, matched, and unmatched counts.
• Investigate repetition before using DISTINCT or aggregation.
• Run EXPLAIN with current statistics and evaluate indexes against the real workload.

Explain it like I'm 5

Imagine two boxes of cards. One box has employee cards and the other has department cards. A join says, “Put cards together when their department numbers match.” An INNER JOIN keeps only matching pairs. A LEFT JOIN keeps every employee card even when no department card fits, leaving the department space blank. A FULL JOIN also keeps lonely cards from both boxes. A CROSS JOIN pairs every employee with every department on purpose. Before accepting the pile, count it: if ten cards suddenly become ten thousand, you probably forgot to explain how they should match.

Exercises

1. Write an INNER JOIN that returns employee ID, employee name, department ID, and department name. Predict the maximum number of department matches per employee.
2. Write a LEFT JOIN that lists every project and any assignments. Then change it to return only projects with no assignments and explain the null test.
3. Create two versions of a department-to-employee LEFT JOIN with STATUS = 'A', once in ON and once in WHERE. Use a department with no active employees and compare the results.
4. Join EMPLOYEE through ASSIGNMENT to PROJECT. Count rows per employee and explain why repeated employee names are legitimate at assignment grain.
5. Write the self join that shows employee and manager names. Explain why LEFT JOIN is preferable when top-level managers have no MANAGER_ID.
6. Produce base counts, joined counts, and unmatched counts for one relationship. State what count would indicate a missing or incomplete predicate.
7. EXPLAIN a join with a selective employee status filter. Review access order, matching index columns, estimated cardinality, and sort activity before proposing an index.