Arvind (parvind_15hotmail.com) writes:Maybe you could explain a little more in detail what results you> select * from titles full outer join publishers on
> titles.pub_id = publishers.pub_id where type = 'business'
> why is there an anamoly in the result sets ??
I don't know if is related to the problem you are seing, but note
that in the SELECT above you have a condition in the WHERE clause.
The logcial execution of A FULL OUTER JOIN B WHERE ... is that first
SQL Server constructs a table containing all rows from A both and B,
and where there is no matching row according to the ON conditions
in B for a row in A, all B columns are NULL and vice versa. Once this
is done, SQL Server applies the WHERE clause. So if the where clause
says A.col = 'Value', this means that all columns with NULL A.col
are filtered away, which means that all rows in B with no matching
rows in A are filtered. If you move the condition in the WHERE
clause to the ON clause, you get a different result.
Note: this is a logical description of how SQL Server evaluates the
query. The actual query may be different, as long as the result is the
Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]
Books Online for SQL Server SP3 at