Outer Join Restrictions

If a table is an inner member of an outer join, it cannot participate in both an outer join clause and a regular join clause.

select distinct sales.stor_id, stor_name, title 
from sales, stores, titles, salesdetail 
where qty > 500
and salesdetail.title_id =* titles.title_id 
and sales.stor_id = salesdetail.stor_id 
and sales.stor_id = stores.stor_id

Msg 303, Level 16, State 1: 
Server ’FUSSY’, Line 1: 
The table ’salesdetail’ is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

If you want to know the name of the store that sold more than 500 copies of a book, you must use a second query. If you submit a query with an outer join and a qualification on a column from the inner table of the outer join, the results may not be what you expect. The qualification in the query does not restrict the number of rows returned, but rather affects which rows contain the null value. For rows that do not meet the qualification, a null value appears in the inner table’s columns of those rows.