Microsoft KB Archive/150322

= INF: ODBC Syntax for Multi-Table Outer Joins =

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