INF: How to Restrict the Inner Table of an ANSI 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.