declare @t1 table (id int, col varchar(32));
declare @t2 table (id int, col nvarchar(32));

insert into @t1 (id, col) values (1,'1');
insert into @t1 (id, col) values (1,'10');

insert into @t2 (id, col) values (1,N'1');
insert into @t2 (id, col) values (2,N'10');

SELECT t1.id, t1.col, t2.id, t2.col FROM @t1 t1
FULL OUTER JOIN @t2 t2 on t1.id=t2.id
WHERE t1.col = t2.col

SELECT t1.id, t1.col, t2.id, t2.col FROM @t1 t1
FULL OUTER JOIN @t2 t2  on t1.id=t2.id
WHERE t1.col <> t2.col

quote:

---

Originally posted by figmo

This works as expected for me:

SELECT t1.id, t1.col, t2.col FROM table1 AS t1
FULL OUTER JOIN table2 as t2 on t1.id=t2.id
WHERE t1.col = t2.col

It returns rows from both tables with matching 'col' data.

This does not work:

SELECT t1.id, t1.col, t2.col FROM table1 AS t1
FULL OUTER JOIN table2 as t2 on t1.id=t2.id
WHERE t1.col <> t2.col

Always returns zero rows.

Can sombody explain why?

There are rows in both tables that have matching cols, and rows in both tables that do not. When put a where clause in for matching columns it works. If I change only the where clause to try to find cols that do not match, it returns an empty result set.

NOTE: I have experimented a little with this. And I tried using the exact same syntax but with a different col. The one listed above giving me trouble is of type nvarchar. I tried the same script using a different column - of type smalldatetime - and it appeared to worked both ways. I could have a where clause of <> and saw only those rows where the smalldatetime col was not equal. So I suspect my syntax is ok.

Is there something special I need to do about nvarchar in this context?

---

SELECT t1.id, t1.col, t2.col FROM table1 AS t1
FULL OUTER JOIN table2 as t2 on t1.id=t2.id
WHERE COALESCE(t1.col,'') <> COALESCE(t2.col,'')