sql join clause is not returning expected results with arabic text-CodePudding

I really been working to solve this problem for quite a while, I have 2 tables with the same structure as follows:

registerationNumber int, CompanyName nvarchar, areaName nvarchar,phoneNumber int , email nvarchar, projectStatus nvarchar

All columns with data type nvarchar contains Arabic text except the email column, tableA contains 675 rows and tableB contains 397 rows all exists in tableA What I am trying to do is select the non matching rows from tableA, they should be 675 - 398 = 277 rows

everytime I run the where clause I get all tables returned

The join clause I am writing is like this:

select a.registerationNumber
from tableA a left outer join tableB b
on a.registerationNumber = b.registerationNumber

but I am not getting any results, I tried all types of joins but I am getting the same results.

I created a sample database and inserted English data in the tables and it worked fine with the following clause:

select * from tblAllProjects a right join tblhalfProjects h 
on a.registerationNumber = h.registerationNumber

which means that I am writing the correct the correct syntax, I know that I should use the following syntax on selecting Arabic text:

Select * from tableA where comanyName like N'arabic_text'

Anyone knows what seems to be the problem ?

CodePudding user response：

I meant to say that you should do:

select a.registerationNumber
from tableA a left outer join tableB b
on a.registerationNumber = b.registerationNumber
where b.registrationNumber is NULL

This should select all registrationNumbers from tableA, with no match in tableB.

another way to write this (but probably slower) is:

select a.registerationNumber
from tableA a 
where a.registerationNumber not in (
   select b.registerationNumber
   from tableB )

You should/can use this second approach if there are only "a few" records returned from the sub-query.