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)