INF: ODBC Syntax for Multi-Table Outer Joins

• Microsoft Open Database Connectivity, versions 2.0x & 3.0x

SUMMARY

This article describes the ODBC syntax for performing outer joins on multiple SQL Server and Microsoft Access tables.

MORE INFORMATION

Chapter 6 of the Microsoft ODBC 2.0 "Programmer's Reference and SDK Guide" has a sub- section on 'Outer Joins' under the section 'Using ODBC Extensions to SQL.' However, the example presented in that section only illustrates a two-table outer join. Below you will find examples for nested outer joins using the SQL Server and Access drivers.

ODBC supports the ANSI SQL-92 left outer join syntax. The shorthand syntax for outer join is:

   {oj outer-join}

   table-name LEFT OUTER JOIN {table-name | outer-join} ON search-condition

An example for the SQL Server driver:

Transact-SQL provides two outer join operators: *=, which includes all rows from the first-named table, and =*, which includes all rows from the second- named table. In Transact-SQL, a select statement for a three-table outer join would look like:

   Select * from A, B,C, where A.col1 *= B.col1 and A.col1 *= C.col1

   Select * from  { oj  A LEFT OUTER JOIN B LEFT OUTER JOIN C
          ON A.col1= B.col1 ON A.col1 = C.col1 }

Though the right outer join ( created using =* ) syntax does not exist in ODBC SQL grammar, you can express a right outer join in terms of a left outer join.

For example, consider this Transact SQL statement:

   Select * from  A, B  where A.col1 =* B.col1  and C.col1=*B.col1

   Select  *  from { oj A LEFT OUTER JOIN B LEFT OUTER JOIN C  ON B.col1 =
                     A.col1 ON B.Col1=C.Col1 }

An example for the Access driver:

The Access-specific syntax for an outer-join is:

   Select-list FROM table1 [ LEFT | RIGHT ] JOIN table2 ON table1.field1 =
   table2.field2

Use a RIGHT JOIN operation to create a right outer join. Right outer joins include all of the records from the second (right-hand) of two tables, even if there are no matching values for records in the first (left-hand) table. Follow the same syntax, explained in the above section, for an equivalent ODBC LEFT OUTER JOIN.

Using the ODBC syntax:

   Select * from { oj  test LEFT OUTER JOIN test1 LEFT
   OUTER JOIN test2
               ON test.col1= test1.col1 ON test.col1 = test2.col1 }

   Select * from test, test1, test2 { oj  test LEFT OUTER JOIN test1 LEFT
   OUTER JOIN test2 ON test.col1= test1.col1 ON test.col1 = test2.col1 }