Omnis Technical Note TNSQ0007

Using Outer Joins with OmnisSQL and the Omnis ODBC Driver

For Omnis 7v3.7.x and Omnis Studio 2.x and later
By Omnis Client Server Group

In addition to standard SELECT statements, OmnisSQL and the Omnis read-only ODBC driver (Omodbc32) also supports left and right outer joins. Outer joins can be used to filter a result set from two tables (whilst preserving the column definitions) and to search the second table for multiple joins with the first table.

In Omnis the syntax for specifying an outer join is:
*= for a left outer join, and
=* for a right outer join.

When a left outer join is specified, one or more rows are returned for each row of the left hand table, depending on the number of matches/joins found in the right hand table. If no matching record is found in the right hand table, the row returned consists of values from the left hand table and NULLs are substituted for values from the right hand table.

When a right outer join is specified, one or more rows are returned for each row of the right hand table, depending on the number of matches/joins found in the left hand table. If no matching record is found in the left hand table, the row returned consists of values from the right hand table and NULLs are substituted for values from the left hand table.

Result columns are returned in column order according to the tables/columns specified in the SELECT statement. They are not affected by the join or the type of join used.

Example
The following tables can be used to illustrate outer joins. In this example, the ITEM column of table 1 (the left hand table) will be joined to the KEY column of table2 (the right hand table).

Table1 - 5 rows

table1.FNAME table1.LNAME table1.ITEM table1.QTY
C Robinson 1000 2
Mr L Hawtry 1000 1
Mrs T Smith 1002 1
F R Hammond 1003 4
G Manning 1003 2

Table2 - 4 rows

table2.KEY table2.ITEMTEXT table2.PRICE
1000 Quartz sink pack white 119.99
1001 Stainless steel 1 1/2in. bowl 109.99
1002 Cast shower tray white 49.99
1003 Mixer taps 1 1/2in. chrome bath 74.99

Left Outer Join
SELECT * FROM table1,table2 WHERE table1.ITEM *= table2.KEY
returns the following results:

table1.FNAME table1.LNAME table1.ITEM table1.QTY table2.KEY table2.ITEMTEXT table2.PRICE
C Robinson 1000 2 1000 Quartz sink pack ... 119.99
Mr L Hawtry 1000 1 1000 Quartz sink pack ... 119.99
Mrs T Smith 1002 1 1002 Cast shower tray ... 49.99
F R Hammond 1003 4 1003 Mixer taps 1 1/2in... 74.99
G Manning 1003 2 1003 Mixer taps 1 1/2in... 74.99

For every row in the left hand table, a row is returned containing the left hand data plus the right hand data for each join found in the right hand table.

Notes.
In this case, values in the KEY field of the right hand table are unique so one row is returned for each row in the left hand table.

Right Outer Join
SELECT * FROM table1,table2 WHERE table1.ITEM =* table2.KEY
returns the following results:

table1.FNAME table1.LNAME table1.ITEM table1.QTY table2.KEY table2.ITEMTEXT table2.PRICE
C Robinson 1000 2 1000 Quartz sink pack ... 119.99
Mr L Hawtry 1000 1 1000 Quartz sink pack ... 119.99
NULL NULL NULL NULL 1001 Stainless steel 1 1/2in... 109.99
Mrs T Smith 1002 1 1002 Cast shower tray ... 49.99
F R Hammond 1003 4 1003 Mixer taps 1 1/2in... 74.99
G Manning 1003 2 1003 Mixer taps 1 1/2in... 74.99

For every row in the right hand table, a row is returned containing the right hand data plus the left hand data for each join found in the left hand table.

Notes.
In this case, there are two matching entries for row one of the right hand table (rows 1 and 2 of the left hand table). There is no matching entry in the left hand table for row two of the right hand table, hence NULLs are substituted. There is one matching entry for row three of the right hand table. There are two matching entries for row four of the right hand table (rows four and five of the left hand table)