In this month's Significa, I explained that IBM has released support for outer joins in SQL as a set of PTFs. The question is, once you load and apply the PTFs, how do you actually code the outer join SQL statement? It is part of the FROM clause, and you can see the syntax diagram in 3 (see page 69).

In this month's Significa, I explained that IBM has released support for outer joins in SQL as a set of PTFs. The question is, once you load and apply the PTFs, how do you actually code the outer join SQL statement? It is part of the FROM clause, and you can see the syntax diagram in Figure 3 (see page 69).

To demonstrate the new support, I have an SQL statement in 4. In the example, I join a file called ITEMS and a file called RETURNS. The statement returns joined records from the two files even if an ITEMS record doesn't have a matching RETURNS record.

To demonstrate the new support, I have an SQL statement in Figure 4. In the example, I join a file called ITEMS and a file called RETURNS. The statement returns joined records from the two files even if an ITEMS record doesn't have a matching RETURNS record.

In 5, you can see the results of 4's SQL statement. For the record of item number 5, there was no matching RETURNS record; the nulls returned showed as dashes. As you can see in the syntax diagram, the new syntax also supports inner and exception joins. Inner joins return only records that have matching records in both files, and exception joins return only records that don't have matching records in both files. It is the outer join support that caught my attention.

In Figure 5, you can see the results of Figure 4's SQL statement. For the record of item number 5, there was no matching RETURNS record; the nulls returned showed as dashes. As you can see in the syntax diagram, the new syntax also supports inner and exception joins. Inner joins return only records that have matching records in both files, and exception joins return only records that don't have matching records in both files. It is the outer join support that caught my attention.

-Jim Hoopes

TechTalk: Using Outer Joins in SQL

Figure 3: Syntax Diagram for Joins

 +------------------------------------------------------------+ | | | | | <-,---------< | | >--FROM----table-ref-------------------------------------> | | | | TABLE-REF: | | |--.-single-table-.--------------------------------------| | | '-join-table---' | | | | SINGLE-TABLE: | | |--.-table-name-.--.--------------------------.----------| | | '-view-name--' | .-AS-. | | | '-'----'--correlation-name-' | | | | JOIN-TABLE: | | |--.-table-ref--.-join-table-2------------.-.------------| | | | '-CROSS JOIN--single-table-' | | | '-(--join-table--)------------------------' | | | | JOIN-TABLE-2: | | |--join-type--JOIN--single-table--ON--join-condition-----| | | | | JOIN-TYPE: | | .-INNER-----------. | | |--+-----------------+-----------------------------------| | | | .-OUTER-. | | | |-LEFT--'-------'-| | | '-EXCEPTION-------' | | | | JOIN-CONDITION: | | <--------------------------< | | |--.-join-predicate-------.---.-----------------------.--| | | '-(--join-condition--)-' '-AND--join-condition-2-' | | | | JOIN-CONDITION-2: | | |--.-join-predicate-------.------------------------------| | | '-(--join-condition--)-' | | | | JOIN-PREDICATE: | | |--expression--.-=--.--expression------------------------| | | |-¬=-| | | |-<>-| | | |->--| | | |->=-| | | |-<--| | | |-<=-| | | |-¬<-| | | '-¬>-' | | | +------------------------------------------------------------+ 
TechTalk: Using Outer Joins in SQL
 
Figure 4: Using Outer Join Support
  
 
TechTalk: Using Outer Joins in SQL
 
Figure 5: Sample Output Using Outer Join