Right Joins

What a Right Join Produces

With a right join, the output has one row for every record in file 2. For each F2 record: if there is at least one record in F1 with the same join key, the output contains one row per match (F1 data plus that F2 record). If there is no F1 record with that key, the output contains one row with the F2 data and the F1 portion blank or zero-filled. So the number of output rows is at least the number of F2 records; it can be more when there are multiple F1 matches per key.

JOIN UNPAIRED,F2

To get a right join, you code JOIN UNPAIRED,F2. This means “include unpaired F2 records in the output”—i.e. do not drop F2 records that have no match in F1. The right (second) file is preserved in full; F1 is optional.

Control Statement Order and Example

The order is: JOINKEYS for F1, JOINKEYS for F2, REFORMAT FIELDS=, then JOIN UNPAIRED,F2, then SORT FIELDS=COPY or SORT FIELDS=. Same as left join except F2 is specified instead of F1.

Example: right join, key in first 10 bytes

1
2
3
4
5
  JOINKEYS F1=SORTJNF1,FIELDS=(1,10,CH,A)
  JOINKEYS F2=SORTJNF2,FIELDS=(1,10,CH,A)
  REFORMAT FIELDS=(F1:1,80,F2:11,70)
  JOIN UNPAIRED,F2
  SORT FIELDS=COPY

Every F2 record is written. When there is a match in F1, the output has 80 bytes from F1 and 70 bytes from F2. When there is no match, the 80 bytes from F1 are blank-filled and the 70 bytes from F2 are real data. Total record length is 150 bytes for every output record.

How Unmatched F2 Records Are Filled

For an F2 record with no matching key in F1, DFSORT builds the full REFORMAT record. The F2:position,length segments contain the actual F2 data. The F1:position,length segments are filled with blanks for character (CH) format and low values for numeric formats. So the output record length is constant; you can detect “no match” by checking whether the F1 portion is blank or zero.

Right Join vs Left Join

Left join (JOIN UNPAIRED,F1): every F1 record appears; F2-only records are dropped. Right join (JOIN UNPAIRED,F2): every F2 record appears; F1-only records are dropped. So the only difference is which file is “driving”: the one that is fully preserved. You can get the same logical set of rows by swapping the two input files and using a left join—the output column order (F1 then F2 in REFORMAT) will reflect the swapped file order.

Join Key in Different Positions

The join key can be in different positions in each file. Specify FIELDS= for each file in its JOINKEYS. For unpaired F2 records, the F1 portion of REFORMAT is still blank or zero-filled.

Example: keys at different positions

1
2
3
4
5
  JOINKEYS F1=MASTER,FIELDS=(1,8,CH,A)
  JOINKEYS F2=TRAN,FIELDS=(20,8,CH,A)
  REFORMAT FIELDS=(F1:1,100,F2:1,50)
  JOIN UNPAIRED,F2
  SORT FIELDS=COPY

Every transaction (F2) record appears. When a master (F1) matches, the first 100 bytes are from F1 and the next 50 from F2. When no master matches, the first 100 bytes are blanks and the next 50 are the transaction data.

When to Use a Right Join

• You need every record from the second file and optional data from the first (e.g. all transactions with optional master or product info, including orphan transactions).
• Your process is built around “F2 is the driver” and you do not want to swap file order or change REFORMAT.
• You want to find F2 records that have no match in F1 (e.g. transactions with no valid master) by checking the blank-filled F1 portion.

Full Outer Join: Including Both Unpaired F1 and F2

If you need all records from both files in one output—both F1-only and F2-only and matched pairs—code JOIN UNPAIRED,F1,F2. That is the full outer join. Unpaired F1 records get blank F2; unpaired F2 records get blank F1. One pass produces the complete result.

JCL: DD Names

By default the first file is SORTJNF1 and the second SORTJNF2. Example:

1
2
3
4
5
6
7
  //STEP1    EXEC PGM=SORT
  //SORTJNF1  DD DSN=MY.MASTER,DISP=SHR
  //SORTJNF2  DD DSN=MY.TRAN,DISP=SHR
  //SORTOUT   DD DSN=MY.RIGHTJOIN,DISP=(NEW,CATLG),...
  //SYSIN     DD *
  ... JOINKEYS, REFORMAT, JOIN UNPAIRED,F2, SORT FIELDS=COPY ...
  /*

Explain It Like I'm Five

You have a list of kids who got a sticker (file 2) and a list of all kids in the class (file 1). You want one list that has every kid who got a sticker, and their name from the big list if they are on it. So you go through the sticker list—every name on that list—and for each one you look on the class list. If they are on the class list, you write their name and “in class.” If they are not on the class list (maybe a typo or a visitor), you still write their name and leave the “in class” part blank. Nobody from the sticker list is left out. That’s the right join: the second list (stickers) is complete; the first list (class) only fills in when there’s a match.

Exercises

1. Write control statements for a right join of MASTER (F1) and TRAN (F2), 6-byte character key at position 1 in both, REFORMAT outputting 50 bytes from F1 and 60 bytes from F2. Include JOIN UNPAIRED,F2.
2. File 1 has 80 records; file 2 has 120. Twenty keys in F2 do not exist in F1. How many output records do you get with a right join? (Assume one record per key in each file.)
3. How could you produce the same set of rows as a right join by using a left join instead? What would you change in JCL and control statements?
4. When would you choose JOIN UNPAIRED,F2 over JOIN UNPAIRED,F1,F2?