When you join two data sources, some records in one
of the files may lack corresponding records in the other file. When
a report omits records that are not in both files, the join is called
an inner join. When a report displays all matching records, plus
all records from the host file that lack corresponding cross-referenced
records, the join is called a left outer join.
The SET ALL command globally determines how all joins are implemented.
If the SET ALL=ON command is issued, all joins are treated as outer
joins. With SET ALL=OFF, the default, all joins are treated as inner
joins.
Each JOIN command can specify explicitly which type of join to
perform, locally overruling the global setting. This syntax is supported
for FOCUS, XFOCUS, Relational, VSAM, IMS, and Adabas. If you do
not specify the type of join in the JOIN command, the ALL parameter
setting determines the type of join to perform.
x
Procedure: How to Create Data Sources Used in Inner and Left Outer Join Examples
The following procedure creates three
FOCUS data sources:
- EMPINFO,
which contains the fields EMP_ID, LAST_NAME, FIRST_NAME, and CURR_JOBCODE
from the EMPINFO segment of the EMPLOYEE data source.
- JOBINFO,
which contains the JOBCODE and JOB_DESC fields from the JOBFILE
data source.
- EDINFO, which
contains the EMP_ID, COURSE_CODE, and COURSE_NAME fields from the
EDUCFILE data source.
The procedure
then adds an employee to EMPINFO named Fred Newman who has no matching
record in the JOBINFO or EDINFO data sources.
TABLE FILE EMPLOYEE
SUM LAST_NAME FIRST_NAME CURR_JOBCODE
BY EMP_ID
ON TABLE HOLD AS EMPINFO FORMAT FOCUS INDEX EMP_ID CURR_JOBCODE
END
-RUN
TABLE FILE JOBFILE
SUM JOB_DESC
BY JOBCODE
ON TABLE HOLD AS JOBINFO FORMAT FOCUS INDEX JOBCODE
END
-RUN
TABLE FILE EDUCFILE
SUM COURSE_CODE COURSE_NAME
BY EMP_ID
ON TABLE HOLD AS EDINFO FORMAT FOCUS INDEX EMP_ID
END
-RUN
MODIFY FILE EMPINFO
FREEFORM EMP_ID LAST_NAME FIRST_NAME CURR_JOBCODE
MATCH EMP_ID
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA
111111111, NEWMAN, FRED, C07,$
END
The following request
prints the contents of EMPINFO. Note that Fred Newman has been added
to the data source:
TABLE FILE EMPINFO
PRINT *
END
The output is:
EMP_ID LAST_NAME FIRST_NAME CURR_JOBCODE
------ --------- ---------- ------------
071382660 STEVENS ALFRED A07
112847612 SMITH MARY B14
117593129 JONES DIANE B03
119265415 SMITH RICHARD A01
119329144 BANNING JOHN A17
123764317 IRVING JOAN A15
126724188 ROMANS ANTHONY B04
219984371 MCCOY JOHN B02
326179357 BLACKWOOD ROSEMARIE B04
451123478 MCKNIGHT ROGER B02
543729165 GREENSPAN MARY A07
818692173 CROSS BARBARA A17
111111111 NEWMAN FRED C07
Example: Creating an Inner Join
The following JOIN command creates an
inner join between the EMPINFO data source and the JOBINFO data
source.
JOIN CLEAR *
JOIN INNER CURR_JOBCODE IN EMPINFO TO MULTIPLE JOBCODE IN JOBINFO AS J0
Note
that the JOIN command specifies a multiple join. In a unique join,
the cross-referenced segment is never considered missing, and all
records from the host file display on the report output. Default
values (blank for alphanumeric fields and zero for numeric fields)
display if no actual data exists.
The
following request displays fields from the joined structure:
TABLE FILE EMPINFO
PRINT LAST_NAME FIRST_NAME JOB_DESC
END
Fred Newman is omitted
from the report output because his job code does not have a match
in the JOBINFO data source:
LAST_NAME FIRST_NAME JOB_DESC
--------- ---------- --------
STEVENS ALFRED SECRETARY
SMITH MARY FILE QUALITY
JONES DIANE PROGRAMMER ANALYST
SMITH RICHARD PRODUCTION CLERK
BANNING JOHN DEPARTMENT MANAGER
IRVING JOAN ASSIST.MANAGER
ROMANS ANTHONY SYSTEMS ANALYST
MCCOY JOHN PROGRAMMER
BLACKWOOD ROSEMARIE SYSTEMS ANALYST
MCKNIGHT ROGER PROGRAMMER
GREENSPAN MARY SECRETARY
CROSS BARBARA DEPARTMENT MANAGER
Example: Creating a Left Outer Join
The following JOIN command creates a
left outer join between the EMPINFO data source and the EDINFO data
source:
JOIN CLEAR *
JOIN LEFT_OUTER EMP_ID IN EMPINFO TO MULTIPLE EMP_ID IN EDINFO AS J1
The following request displays fields
from the joined structure:
TABLE FILE EMPINFO
PRINT LAST_NAME FIRST_NAME COURSE_NAME
END
All employee records display
on the report output. The records for those employees with no matching
records in the EDINFO data source display the missing data character
(.) in the COURSE_NAME column. If the join were unique, blanks would
display instead of the missing data character.
LAST_NAME FIRST_NAME COURSE_NAME
--------- ---------- -----------
STEVENS ALFRED FILE DESCRPT & MAINT
SMITH MARY BASIC REPORT PREP FOR PROG
JONES DIANE FOCUS INTERNALS
SMITH RICHARD BASIC RPT NON-DP MGRS
BANNING JOHN .
IRVING JOAN .
ROMANS ANTHONY .
MCCOY JOHN .
BLACKWOOD ROSEMARIE DECISION SUPPORT WORKSHOP
MCKNIGHT ROGER FILE DESCRPT & MAINT
GREENSPAN MARY .
CROSS BARBARA HOST LANGUAGE INTERFACE
NEWMAN FRED .
Example: Creating Two Inner Joins With a Multipath Structure
The following JOIN commands create an
inner join between the EMPINFO and JOBINFO data sources and an inner
join between the EMPINFO and EDINFO data sources:
JOIN CLEAR *
JOIN INNER CURR_JOBCODE IN EMPINFO TO MULTIPLE JOBCODE IN JOBINFO AS J0
JOIN INNER EMP_ID IN EMPINFO TO MULTIPLE EMP_ID IN EDINFO AS J1
The structure created by the two joins
has two independent paths:
SEG01
01 S1
**************
*EMP_ID **I
*CURR_JOBCODE**I
*LAST_NAME **
*FIRST_NAME **
* **
***************
**************
I
+-----------------+
I I
I SEG01 I SEG01
02 I KM 03 I KM
.............. ..............
:EMP_ID ::K :JOBCODE ::K
:COURSE_CODE :: :JOB_DESC ::
:COURSE_NAME :: : ::
: :: : ::
: :: : ::
:............:: :............::
.............: .............:
JOINED EDINFO JOINED JOBINFOThe
following request displays fields from the joined structure:
SET MULTIPATH=SIMPLE
TABLE FILE EMPINFO
PRINT LAST_NAME FIRST_NAME IN 12 COURSE_NAME JOB_DESC
END
With MULTIPATH=SIMPLE,
the independent paths create independent joins. All employee records
accepted by either join display on the report output. Only Fred
Newman (who has no matching record in either of the cross-referenced
files) is omitted:
LAST_NAME FIRST_NAME COURSE_NAME JOB_DESC
--------- ---------- ----------- --------
STEVENS ALFRED FILE DESCRPT & MAINT SECRETARY
SMITH MARY BASIC REPORT PREP FOR PROG FILE QUALITY
JONES DIANE FOCUS INTERNALS PROGRAMMER ANALYST
SMITH RICHARD BASIC RPT NON-DP MGRS PRODUCTION CLERK
BANNING JOHN . DEPARTMENT MANAGER
IRVING JOAN . ASSIST.MANAGER
ROMANS ANTHONY . SYSTEMS ANALYST
MCCOY JOHN . PROGRAMMER
BLACKWOOD ROSEMARIE DECISION SUPPORT WORKSHOP SYSTEMS ANALYST
MCKNIGHT ROGER FILE DESCRPT & MAINT PROGRAMMER
GREENSPAN MARY . SECRETARY
CROSS BARBARA HOST LANGUAGE INTERFACE DEPARTMENT MANAGER
With MULTIPATH=COMPOUND, only employees
with matching records in both of the cross-referenced files display
on the report output:
LAST_NAME FIRST_NAME COURSE_NAME JOB_DESC
--------- ---------- ----------- --------
STEVENS ALFRED FILE DESCRPT & MAINT SECRETARY
SMITH MARY BASIC REPORT PREP FOR PROG FILE QUALITY
JONES DIANE FOCUS INTERNALS PROGRAMMER ANALYST
SMITH RICHARD BASIC RPT NON-DP MGRS PRODUCTION CLERK
BLACKWOOD ROSEMARIE DECISION SUPPORT WORKSHOP SYSTEMS ANALYST
MCKNIGHT ROGER FILE DESCRPT & MAINT PROGRAMMER
CROSS BARBARA HOST LANGUAGE INTERFACE DEPARTMENT MANAGER
Example: Creating DB2 Data Sources Used in Join Examples
The following procedure creates DB2
data sources to use in join examples. This assumes the Master Files
and Access Files for EMPINFO, JOBINFO, and EDINFO already exist
and that you have the authority to create tables:
TABLE FILE EMPLOYEE
SUM LAST_NAME FIRST_NAME CURR_JOBCODE
BY EMP_ID
ON TABLE HOLD AS EMPF FORMAT FOCUS
END
-RUN
TABLE FILE JOBFILE
SUM JOB_DESC
BY JOBCODE
ON TABLE HOLD AS JOB1 FORMAT ALPHA
END
-RUN
TABLE FILE EDUCFILE
SUM COURSE_CODE COURSE_NAME
BY EMP_ID
ON TABLE HOLD AS ED1 FORMAT ALPHA
END
-RUN
-*Create new record with no match in job or ed files
MODIFY FILE EMPF
FREEFORM EMP_ID LAST_NAME FIRST_NAME CURR_JOBCODE
MATCH EMP_ID
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA
111111111, NEWMAN, FRED, C07,$
END
-RUN
TABLE FILE EMPF
PRINT *
ON TABLE HOLD AS EMP1 FORMAT ALPHA
END
-RUN
-*Create DB2 tables
CREATE FILE EMPINFO
-RUN
CREATE FILE JOBINFO
-RUN
CREATE FILE EDINFO
-RUN
-*Load DB2 tables
MODIFY FILE EMPINFO
FIXFORM FROM EMP1
DATA ON EMP1
END
-RUN
MODIFY FILE JOBINFO
FIXFORM FROM JOB1
DATA ON JOB1
END
-RUN
MODIFY FILE EDINFO
FIXFORM FROM ED1
DATA ON ED1
END
-RUN
Example: Optimizing an Outer Join Between DB2 Data Sources
The following request joins the EMPINFO
table to the JOBINFO table. The SET SQLJOIN OUTER ON command is
required for DB2, which has OFF as its default value:
ENGINE DB2 SET SQLJOIN OUTER ON
SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON = STMTRACE//CLIENT
-RUN
JOIN CLEAR *
-RUN
JOIN
LEFT_OUTER CURR_JOBCODE IN EMPINFO TO MULTIPLE JOBCODE IN JOBINFO AS J0
END
TABLE FILE EMPINFO
PRINT LAST_NAME FIRST_NAME IN 12 JOB_DESC
END
The single SQL SELECT
statement for both tables shows that the join is optimized:
SELECT T1."LN",T1."FN",T1."CJC",T2."JCD",T2."JDS" FROM
(USERID1."EMPINFO" T1 LEFT OUTER JOIN USERID1."JOBINFO" T2 ON
T2."JCD" = T1."CJC") FOR FETCH ONLY;
Fred Newman is included although he
has no matching record in the JOBINFO data source. His missing job
description is indicated by the NODATA character (.):
LAST_NAME FIRST_NAME JOB_DESC
--------- ---------- --------
SMITH RICHARD PRODUCTION CLERK
GREENSPAN MARY SECRETARY
STEVENS ALFRED SECRETARY
IRVING JOAN ASSIST.MANAGER
BANNING JOHN DEPARTMENT MANAGER
CROSS BARBARA DEPARTMENT MANAGER
MCKNIGHT ROGER PROGRAMMER
MCCOY JOHN PROGRAMMER
JONES DIANE PROGRAMMER ANALYST
BLACKWOOD ROSEMARIE SYSTEMS ANALYST
ROMANS ANTHONY SYSTEMS ANALYST
SMITH MARY FILE QUALITY
NEWMAN FRED .
Example: Optimizing Two Outer Joins Between DB2 Data Sources
The following request joins the EMPINFO
table to the JOBINFO table and the EDINFO table with the setting
MULTIPATH=COMPOUND. The SET SQLJOIN OUTER ON command is required
for DB2, which has OFF as its default value:
ENGINE DB2 SET SQLJOIN OUTER ON
SET MULTIPATH=COMPOUND
-RUN
SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON = STMTRACE//CLIENT
-RUN
JOIN CLEAR *
-RUN
JOIN
LEFT_OUTER CURR_JOBCODE IN EMPINFO TO MULTIPLE JOBCODE IN JOBINFO AS J0
END
JOIN
LEFT_OUTER EMP_ID IN EMPINFO TO MULTIPLE EMP_ID IN EDINFO AS J1
END
TABLE FILE EMPINFO
PRINT LAST_NAME FIRST_NAME IN 12 COURSE_NAME JOB_DESC
END
The single SQL SELECT
statement incorporating all tables shows that the join is optimized:
SELECT T1."EID",T1."LN",T1."FN",T1."CJC",T2."E01",T2."CNM",
T3."JCD",T3."JDS" FROM ( ( USERID1."EMPINFO" T1 LEFT OUTER JOIN
USERID1."EDINFO" T2 ON T2."E01" = T1."EID" ) LEFT OUTER JOIN
USERID1."JOBINFO" T3 ON T3."JCD" = T1."CJC" ) FOR FETCH ONLY;
All employees are included on the report
output. Missing course names and job descriptions are indicated
by the NODATA character (.):
LAST_NAME FIRST_NAME COURSE_NAME JOB_DESC
--------- ---------- ----------- --------
SMITH RICHARD BASIC RPT NON-DP MGRS PRODUCTION CLERK
GREENSPAN MARY . SECRETARY
STEVENS ALFRED FILE DESCRPT & MAINT SECRETARY
IRVING JOAN . ASSIST.MANAGER
BANNING JOHN . DEPARTMENT MANAGER
CROSS BARBARA HOST LANGUAGE INTERFACE DEPARTMENT MANAGER
MCKNIGHT ROGER FILE DESCRPT & MAINT PROGRAMMER
MCCOY JOHN . PROGRAMMER
JONES DIANE FOCUS INTERNALS PROGRAMMER ANALYST
ROMANS ANTHONY . SYSTEMS ANALYST
BLACKWOOD ROSEMARIE DECISION SUPPORT WORKSHOP SYSTEMS ANALYST
SMITH MARY BASIC REPORT PREP FOR PROG FILE QUALITY
NEWMAN FRED . .
