转换为整数后在字符串上JOIN(JOIN on a String After Converted to Integer)

 我在CHAR转换为INT后尝试LEFT JOIN 。 喜欢这个： 
 
SELECT o.title, d.title FROM original o
LEFT JOIN directory d ON CONVERT(o.directory_index, UNSIGNED INT) = d.index
 
 如果索引大于零，这给了我正确的响应。 但是如果索引为零或空字符串，我会为每个目录标题获取行的副本： 
 
o.title       (o.index)    d.title          (d.index)

"Big Boss"    "2"          "OUTER HEAVEN"    2
"Snake"       "0"          "FOX"             0
"Snake"       "0"          "FOXHOUND"        1
"Snake"       "0"          "OUTER HEAVEN"    2
"Kerotan"     ""           "FOX"             0
"Kerotan"     ""           "FOXHOUND"        1
"Kerotan"     ""           "OUTER HEAVEN"    2
 
 所以我添加了一个COALESCE语句将每个空字符串转换为NULL值： 
 
SELECT o.title, d.title FROM original o
LEFT JOIN directory d ON CONVERT(
    CASE WHEN COALESCE(o.directory_index, '') = '' THEN NULL ELSE o.directory_index END,
    UNSIGNED INT
) = d.index
 
 COALESCE语句确实给了我正确的响应（我单独测试了函数的输出），但现在我为每个目录标题获取NULL ，除非索引设置为0 ，在这种情况下，我得到与以前相同的结果： 
 
o.title       (o.index)    d.title          (d.index)

"Big Boss"    "2"          NULL              2
"Snake"       "0"          "FOX"             0
"Snake"       "0"          "FOXHOUND"        1
"Snake"       "0"          "OUTER HEAVEN"    2
"Kerotan"     ""           NULL              0
"Kerotan"     ""           NULL              1
"Kerotan"     ""           NULL              2 
 
 我究竟做错了什么？ 
 
 
 正如所建议的那样，我摆脱了COALESCE语句，并将其换成了NULLIF语句： 
 
SELECT o.title, d.title FROM original o
LEFT JOIN directory d ON CONVERT(
    NULLIF(o.directory_index, ''),
    UNSIGNED INT
) = d.index
 
 但是我遇到的结果与我的COALESCE声明相同。 
 
 我创建了一个我提供的示例的小提琴 ，奇怪的是它给了我想要的结果。 但这不是我在Workbench中获得的结果。 服务器正在运行旧版本的MySQL（我认为5.1？）可能是问题吗？ 
 
 
 好的...所以我很尴尬。 昨天我整天都在撞墙。 然后我今天早上来看看它，原来应该是LEFT JOIN ... ON ... = d.index设置为LEFT JOIN ... ON ... = d.title 。 我将在这个帖子上向所有人投票。 但我希望你们都投票给我和/或标记要关闭的问题。 

I am trying to LEFT JOIN on a CHAR after it is converted to an INT. Like this:
 
SELECT o.title, d.title FROM original o
LEFT JOIN directory d ON CONVERT(o.directory_index, UNSIGNED INT) = d.index
 
This gives me the correct response if the index is greater than zero. But if the index is zero or an empty string, I get duplicates of the row for every directory title:
 
o.title       (o.index)    d.title          (d.index)

"Big Boss"    "2"          "OUTER HEAVEN"    2
"Snake"       "0"          "FOX"             0
"Snake"       "0"          "FOXHOUND"        1
"Snake"       "0"          "OUTER HEAVEN"    2
"Kerotan"     ""           "FOX"             0
"Kerotan"     ""           "FOXHOUND"        1
"Kerotan"     ""           "OUTER HEAVEN"    2
 
So I added a COALESCE statement to convert every empty string to a NULL value:
 
SELECT o.title, d.title FROM original o
LEFT JOIN directory d ON CONVERT(
    CASE WHEN COALESCE(o.directory_index, '') = '' THEN NULL ELSE o.directory_index END,
    UNSIGNED INT
) = d.index
 
The COALESCE statement does give me the correct response (I tested the output of the function alone), but now I get NULL for every directory title, unless the index was set to 0, in which case I get the same result as before:
 
o.title       (o.index)    d.title          (d.index)

"Big Boss"    "2"          NULL              2
"Snake"       "0"          "FOX"             0
"Snake"       "0"          "FOXHOUND"        1
"Snake"       "0"          "OUTER HEAVEN"    2
"Kerotan"     ""           NULL              0
"Kerotan"     ""           NULL              1
"Kerotan"     ""           NULL              2 
 
What am I doing wrong?
 
 
As suggested, I got rid of the COALESCE statement, and swapped it out for a NULLIF statement:
 
SELECT o.title, d.title FROM original o
LEFT JOIN directory d ON CONVERT(
    NULLIF(o.directory_index, ''),
    UNSIGNED INT
) = d.index
 
But I am met with the same result as if I had the COALESCE statement.
 
I created a Fiddle of the examples I have provided, and strangely enough it gives me my desired result. But this is not the result I am getting in Workbench. The server is running an older version of MySQL (5.1 I think?) could that be the problem?
 
 
Okay... so I am pretty embarrassed. I was banging my head against the wall all day yesterday. Then I come in this morning and take a look at it, it turns out what was supposed to be LEFT JOIN ... ON ... = d.index was set to LEFT JOIN ... ON ... = d.title. I am going to up-vote everyone on this thread. But I hope you all down vote me and/or mark the question to be closed.

原文：https://stackoverflow.com/questions/36631823