Handling Unmatched Records

Default: Inner Join (Unmatched Dropped)

If you do not code JOIN UNPAIRED, only records that have a matching key in both files are written. An F1 record with no F2 match is dropped. An F2 record with no F1 match is dropped. So the output contains only paired rows. That is the inner join and is the default.

JOIN UNPAIRED: Keep Unmatched Rows

JOIN UNPAIRED tells DFSORT to include unpaired records in the output. You specify which file(s) to preserve:

• JOIN UNPAIRED,F1 — Keep every record from the first file. When an F1 record has no match in F2, write one output row with the F1 data and the F2 portion blank-filled (or low values for numeric). This is a left outer join.
• JOIN UNPAIRED,F2 — Keep every record from the second file. When an F2 record has no match in F1, write one output row with the F2 data and the F1 portion blank-filled. This is a right outer join.
• JOIN UNPAIRED,F1,F2 — Keep all records from both files. Unpaired F1 get blank F2; unpaired F2 get blank F1. This is a full outer join.

How the "Missing" Side Is Filled

REFORMAT defines the output layout as a sequence of F1: and F2: segments. For an unpaired F1 record, the F1 segment contains the actual F1 data; the F2 segment(s) are filled with blanks (for character data) or low values (e.g. zeros for numeric fields). For an unpaired F2 record, the F2 segment has the actual data and the F1 segment(s) are blank/low. So every output record has the same length and layout; you can tell unpaired rows by testing whether the "other" file’s portion is blank or zero.

Example: Left Join with Unmatched F1

1
2
3
4
5
  JOINKEYS F1=CUSTOMER,FIELDS=(1,8,CH,A)
  JOINKEYS F2=ORDERS,FIELDS=(15,8,CH,A)
  REFORMAT FIELDS=(F1:1,80,F2:1,60)
  JOIN UNPAIRED,F1
  SORT FIELDS=COPY

Every customer (F1) appears. Customers with orders get 80 bytes of customer data plus 60 bytes of order data (one row per order for one-to-many). Customers with no orders get one row each: 80 bytes of customer data and 60 bytes of blanks in the F2 area. Downstream you can select "F2 all blank" to list customers with no orders.

Identifying Unpaired Rows Downstream

To isolate unpaired rows (e.g. for a report or separate file), use a follow-on step. For a left join, rows where the F2 portion is all blanks (or all zeros if numeric) are F1-only. You can use DFSORT INCLUDE/OMIT on that portion, or a COBOL/Assembler program that tests the F2 segment. For a full outer join, you have both: F1-only (F2 blank) and F2-only (F1 blank). A common pattern is to run the join with JOIN UNPAIRED, then OUTFIL with INCLUDE/OMIT or SPLIT to write matched vs unmatched to different datasets based on a test of the blank/low portion.

Order of Statements

JOIN UNPAIRED must appear after both JOINKEYS and REFORMAT. Typical order: JOINKEYS F1=..., JOINKEYS F2=..., REFORMAT FIELDS=..., JOIN UNPAIRED,F1 (or F2 or F1,F2), SORT FIELDS=COPY or SORT FIELDS=.

When to Use Each Join Type

• Inner (no JOIN UNPAIRED): When you only want rows that have a match in both files (e.g. orders with valid customer). Unmatched are discarded.
• Left (JOIN UNPAIRED,F1): When you want every row from the "left" file and matching data from the right when it exists (e.g. all customers, with order data when they have orders). Good for "list everyone, show detail when present."
• Right (JOIN UNPAIRED,F2): When you want every row from the "right" file (e.g. all orders, with customer data when the customer exists).
• Full outer (JOIN UNPAIRED,F1,F2): When you need a complete picture of both files: matched pairs plus F1-only and F2-only rows. Useful for reconciliation or gap analysis.

Explain It Like I'm Five

You have a list of kids and a list of toys. Matching means same name. If you only keep pairs (kid + toy when both have that name), that’s inner—kids with no toy and toys with no kid are thrown away. If you keep every kid and add toy info when there is a toy (otherwise leave the toy part blank), that’s left join. If you keep every toy and add kid info when there is a kid (otherwise blank), that’s right join. If you keep every kid and every toy, with the other side blank when there’s no match, that’s full outer. Handling unmatched means deciding which of these you want and then spotting the blank parts later.

Exercises

1. You need a list of all customers and their last order date; customers with no orders should still appear with a blank order date. Which join type and what do you code?
2. REFORMAT is F1:1,50 and F2:1,40. How do you detect in the output that a row came from an F1 record with no F2 match?
3. You want three outputs: matched pairs, F1-only rows, F2-only rows. How can you get them (one join step plus follow-on, or two joins)?
4. What is the difference between JOIN UNPAIRED,F1 and JOIN UNPAIRED,F1,F2 in terms of which records appear in the output?