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

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 : kbusage SSrvISQL SSrvTran_SQL
Version : WINNT:6.5
Platform : WINDOWS
Issue type : kbhowto