Join statement in JCL

Prerequisite – Job Control Language (JCL)
In RDBMS, SQL JOIN clause is used to join tables and perform multiple operations on it. To perform operations on records in different flat files, JOINKEYS are used. With the help of JOINKEYS in SORT JCL, various join operation on matched and non-matched records can be executed based on matching fields or Keys. Joining can be performed in number of ways like inner join, full outer join, left outer join, right outer join and unpaired combinations.

The join operation is controlled by three important control statements JOINKEYS, JOIN, and REFORMAT. The SORTJNF1 and SORTJNF2, are the DD statements used to specify the files which will be use to perform join operation.

Control Statements :

  1. JOINKEYS FILES=F1, FIELDS=(1, 10, A) – Specifies the file1 join criteria.
  2. JOINKEYS FILES=F2, FIELDS=(1, 10, A) – Specifies the file2 join criteria.
  3. REFORMAT FIELDS=(F1:1, 71, F2:1, 9) – REFORMAT fields tells the sort utility, what fields to be written into output file from the input files by specifying starting and ending position.

JCL SORT to join two files and writes records from both files :

  1. Matched Records (Inner Join) –
    If join statement not specified in sort card, paired records from F1 and F2 are written into output file.



    //SYSIN DD *
            SORT FIELDS=COPY
            JOINKEYS FILES=F1, FIELDS=(1, 10, A)
            JOINKEYS FILES=F2, FIELDS=(1, 10, A)
            REFORMAT FIELDS=(F1:1, 71, F2:1, 9)
    /*
  2. Matched Records and Non Matched Records from File1 –
    Sort card to retain unpaired records from F1 file along with paired records. This type of join is called a LEFT OUTER JOIN.

    //SYSIN DD *
            SORT FIELDS=COPY
            JOINKEYS FILES=F1, FIELDS=(1, 10, A)
            JOINKEYS FILES=F2, FIELDS=(1, 10, A)
            JOIN UNPAIRED, F1
            REFORMAT FIELDS=(F1:1, 71, F2:1, 9)
    /*
  3. Non Matched from File1 –
    Sort card to retain unpaired records from F1 file, cannot use reformat in F2.

    //SYSIN DD * 
            SORT FIELDS=COPY
            JOINKEYS FILES=F1, FIELDS=(1, 10, A)
            JOINKEYS FILES=F2, FIELDS=(1, 10, A)
            JOIN UNPAIRED, F1, ONLY
            REFORMAT FIELDS=(F1:1, 71)
    /*
  4. Matched Records and Non Matched from File2 –
    Sort card to retain unpaired records from F2 file along with paired records. This type of join is called RIGHT OUTER JOIN.

    //SYSIN DD *
            SORT FIELDS=COPY
            JOINKEYS FILES=F1, FIELDS=(1, 10, A)
            JOINKEYS FILES=F2, FIELDS=(1, 10, A)
            JOIN UNPAIRED, F2
            REFORMAT FIELDS=(F1:1, 71, F2:1, 9)
    /*
  5. Non Matched from File2 –
    Sort card to retain unpaired records from F2 file, cannot use reformat in F1.

    //SYSIN DD *
            SORT FIELDS=COPY
            JOINKEYS FILES=F1, FIELDS=(1, 10, A)
            JOINKEYS FILES=F2, FIELDS=(1, 10, A)
            JOIN UNPAIRED, F2, ONLY
            REFORMAT FIELDS=(F2:1, 9)
    /*
  6. Matched Records and Non Matched Records from both files –
    Sort card to retain unpaired records from both F1 and F2 files along with paired records. This type of join is called as FULL OUTER JOIN.

    //SYSIN DD *
            SORT FIELDS=COPY
            JOINKEYS FILES=F1, FIELDS=(1, 10, A)
            JOINKEYS FILES=F2, FIELDS=(1, 10, A)
            JOIN UNPAIRED, F1, F2
            REFORMAT FIELDS=(F1:1, 71, F2:1, 9)
    /*
  7. Non Matched Records from both files –
    Sort card to retain only unpaired records from F1 and F2 files.

    //SYSIN DD *
            SORT FIELDS=COPY
            JOINKEYS FILES=F1, FIELDS=(1, 10, A)
            JOINKEYS FILES=F2, FIELDS=(1, 10, A)
            JOIN UNPAIRED, F1, F2, ONLY or JOIN UNPAIRED, ONLY
            REFORMAT FIELDS=(F1:1, 71, F2:1, 9)
    /*

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.