INF: ODBC Syntax for Multi-Table Outer Joins

Last reviewed: September 9, 1996
Article ID: Q150322
The information in this article applies to:
  • 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}

where outer-join is:

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

where search-condition specifies the join condition between the table-names and the ON clause determines which tables are involved in the outer join.

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

The equivalent ODBC OUTER JOIN syntax is:

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

You can use the above syntax to construct any valid outer-join statement allowed by the SQL Server. For information on outer-join restrictions, please refer to Chapter 4 of the SQL Server "Database Developer's Companion."

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

This translates in ODBC syntax to:

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

You can set the 4032 SQL Server trace flag (using DBCC TRACEON command) and check the SQL Server errorlog to see what the above ODBC syntax has been translated to by the driver. This will enable you to verify whether or not the ODBC syntax you have used conforms to the Transact-SQL that you intended to use.

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 LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left-hand) of two tables, even if there are no matching values for records in the second (right-hand) table.

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 }

NOTE: When you use the Access 2.0 ODBC driver, you may need to list all the tables referenced in JOIN in the FROM clause. For example:

   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 }


Additional reference words: 6.00 sql6 6.50 2.00 3.00 join query mfc rdo sql
server access
KBCategory: kbinterop kbprg
KBSubcategory: ssrvprog odbc




THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 9, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.