Microsoft KB Archive/176480

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

Article ID: 176480

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q176480



SUMMARY
SQL Server 6.5 and later support 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: kbhowto kbsqlserv2000 kbusage KB176480

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.