在内部连接中使用。

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

来自数据库A的id是int，来自数据库B的id是char(25)，其形式为:Item。例如,如果一个。Id是42岁,B。Id是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.

B.Id返回null。

Why is this happening?

为什么会这样?

3 个解决方案

#1

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.

否则，你实际上是在做一个交叉连接，因为你没有在B的连接子句中引用a。

#2

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

无长度说明符的NVARCHAR被视为NVARCHAR(1)，它不能容纳42并将其截断为4。

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

SELECT  @id

--
4

Specify a greater length for @id:

为@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.

这比类似的好，因为等式运算符是可分配的。

#3

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?

如果不使用通配符'%'，为什么要使用LIKE ?，为什么要使用id的静态值进行连接?

#1