Distributed FULL OUTER JOIN results in INNER join

Hi,
I am trying to perform a full outer join across two linked
servers - LINK1 and LINK2. If I put the following query in:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
- the query plan gives me a MERGE JOIN/FULL OUTER JOIN
which is what I expect. If, however, I add a WHERE clause:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
WHERE C1.r = 0 and C2.r = 0
- I get a MERGE JOIN/INNER JOIN, which gives me the wrong
result. The table definition is:
create table test (n char(8) not null, s int not null, r
tinyint not null)
go
alter table test add primary key (n,s)
go
I have obviously simplified the problem as much as
possible. Am I misunderstanding something about FULL OUTER
JOINS or is this a feature? I can obviously circumvent by
selecting into two temporary tables on the local server
and doing the join there.
regards
KenMany thanks
Ken
>--Original Message--
>The problem is nothing to do with the query being a
distributed one. Because
>your WHERE clause references both sides of the join, the
result set will
>include only the INNER joined rows - WHERE C1.r = 0 AND
C2.r = 0 means that
>both r columns must be non-NULL in the result. Perhaps
what you intended
>was:
>...
>WHERE COALESCE(C1.r, C2.r)=0 AND COALESCE(C2.r, C1.r)=0
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>