What else if not OUTER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

What else if not OUTER JOIN

Author

Topic

CanadaDBA

583 Posts

Posted - 2011-08-29 : 16:00:38

Hi,

I have two tables; T1 and T2.
T1 has a P1 coulmn [nvarchar](50) NULL.
T2 has a P2 column [char](10) NULL.

The values for P1 and P2 are phone numbers 10 digits. I want to list all mismatch rows; i.e. those phone numbers existing in T1 and not in T2 and vise versa.


SELECT P1, P2 FROM T1
FULL OUTER JOIN T2 ON T1.P1 = T2.P2
WHERE (T1.P1 IS Null) OR (T2.P2 IS Null)

The result has some P2s which P1 is Null.


P1            P2
------------- -------------
Null          9021234567

But if I look up T1, P2 exists in T1. Why this happens? I tried RIGHT OUTER JOIN but was the same. Any suggestion?

I can rewrite the query using look up the other table and without using JOIN. Would it be slower than using JOIN?

Thanks!

Canada DBA

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-29 : 16:40:21

From what I can tell this should work. One possibility is that you may have pre-fixed spaces or other invisible characters in the T1 table. If all the values in column P1 should be 10 characters long, you can test this theory by running the following query:

select * from T1 where len('X'+P1+'X') <> 12

Also, since P1 column is unicode and P2 is not, it is possible that the characters are not the same. You can try to join after casting P1 to char(10):

FULL OUTER JOIN T2 ON cast(T1.P1 as char(10)) = T2.P2

CanadaDBA

583 Posts

Posted - 2011-08-30 : 16:15:42

Thank you very much for the feed back sunitabeck.
I was suspect to invisible characters too but found the issue. The T1 and T2 in my original query are a set of JOINT tables using linked servers. The issue was that one of the tables in the "T2 set" was using a linked server used in "T1 set"; i.e. it was pointing to a table in T1 instead of T2. The issue is fixed and it wasn't releated to OUTER JOINT.

Thanks again!

Canada DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources