Developers

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)