CODEWITHSUNDEEP
sqlsql-server
dcarneiro
dcarneiro

Reputation: 7180

using like in an inner join

I have to sync data between two databases (on the same server) and to achieve that I'm using a store procedure.

The id from database A is int, the id from database B is char(25) and takes the following form: Item. Example, if A.Id is 42, B.Id is Item42

While doing the query to join both databases the strangest behaviour happens:

SELECT A.Id, B.id
FROM A.dbo.table as A
LEFT OUTER JOIN B.dbo.table as B on (B.id like 'Item42')

works like expected but

DECLARE @id nvarchar;    
SET @id = '42';

SELECT A.Id, B.id
FROM A.dbo.table as A
LEFT OUTER JOIN B.dbo.table as B on (B.id like 'Item' + @id)

returns null on B.Id.

Why is this happening?

Upvotes: 2

Views: 1291

Answers (3)

Quassnoi
Quassnoi

Reputation: 425833

NVARCHAR without length specifier is treated as NVARCHAR(1) which cannot accomodate 42 and truncates it to 4.

DECLARE @id nvarchar;    
SET @id = '42';

SELECT  @id

--
4

Specify a greater length for @id:

DECLARE @id NVARCHAR(20)

Ultimately, your query should look like this:

SELECT  A.Id, B.id
FROM    A.dbo.table as A
LEFT OUTER JOIN
        B.dbo.table as B
ON      B.id = 'Item' + CAST(a.id AS VARCHAR(20))

This is better than LIKE since equality operator is sargable.

Upvotes: 2

Lamak
Lamak

Reputation: 70678

Shouldn't you be doing something like this?:

SELECT A.Id, B.id
FROM A.dbo.table as A
LEFT OUTER JOIN B.dbo.table as B 
ON B.id = 'Item' + CAST(A.id AS VARCHAR(10))

Why are you using a LIKE if you are not using a wildcard '%'?, and why are you doing a JOIN with a static value for id?

Upvotes: 1

John Gibb
John Gibb

Reputation: 10763

I think you want something like this:

select a.id, b.id
from A.dbo.table as a
left join B.dbo.table as b
  on b.id = 'Item' + convert(varchar, a.id)

Otherwise, you're really doing a cross join, since you're not referencing A in B's join clause.

Upvotes: 2

Related Questions