INF: How to Restrict the Inner Table of an ANSI Outer Join

 Last reviewed: November 10, 1997
Article ID: Q176480
The information in this article applies to:
  • Microsoft SQL Server version 6.5

SUMMARY

SQL Server 6.5 supports the ANSI-style join syntax for cross, inner, and outer joins. Restricting the inner table of an ANSI-style outer join may appear to return results that differ from those returned using the Transact- SQL-style outer join syntax. This article discusses the situation in which an ANSI-style outer join appears to return results different from the Transact-SQL-style outer join syntax, and demonstrates the correct syntax to use for an ANSI-style outer join.

MORE INFORMATION

An outer join allows you to restrict the rows in one table, while not restricting the rows in another table. Consider the following Transact-SQL- style example, using the pubs database: 

   SELECT titles.title_id, title, qty
   FROM titles, sales
   WHERE titles.title_id *= sales.title_id
This query would return at least one row for every title, and additional rows if there are multiple sales for a single title. Titles with no sales would display a NULL in the "qty" column.

Because future versions of SQL Server may discontinue support for the "*=" and "=*" outer join operators, it is recommended that you use the ANSI- standard join clauses. The equivalent ANSI-style query for the query above is: 

   SELECT titles.title_id, title, qty
   FROM titles LEFT OUTER JOIN sales
   ON titles.title_id = sales.title_id
If you wanted to restrict the sales table to only return sales for a particular store, you would qualify the inner table, as in the following example: 

   SELECT titles.title_id, title, qty
   FROM titles, sales
   WHERE titles.title_id *= sales.title_id
   AND stor_id = '7066'
This would return 18 rows. To apply the same restriction with the ANSI- style syntax, you might use a query similar to the following: 

   SELECT titles.title_id, title, qty
   FROM titles LEFT OUTER JOIN sales
   ON titles.title_id = sales.title_id
   WHERE stor_id = '7066'
However, in this case, only two rows are returned because the restriction on stor_id is applied after the outer join has been performed. Therefore, titles that do not have sales in the specified store will not appear in the query. The outer join does not appear to have been performed.

The proper way to restrict the inner table of an outer join is to place the inner table qualifier in the ON portion of the FROM clause, as in the following example: 

   SELECT titles.title_id, title, qty
   FROM titles LEFT OUTER JOIN sales
   ON titles.title_id = sales.title_id
   AND stor_id = '7066'
The restriction on the inner table will be applied in the formulation of the outer join, and the result will be equivalent to the Transact-SQL-style syntax example.

Additional query words: outerjoin right outer join tsql t-sql tran-sql
transql transsql trans-sql
Keywords : SSrvISQL SSrvTran_SQL kbusage
Version : WINNT:6.5
Platform : WINDOWS
Issue type : kbhowto
Solution Type : Info_Provided

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: November 10, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.