为什么 SQL Join 会发现两个长度不同的字符串相等？

发布于 2024-10-19 16:12:47 字数 259 浏览 6 评论 0原文

我创建了一个使用左连接的查询。由于我不明白的原因，它将连接两个不同长度的字符串，就好像它们相等一样。一个例子是：

Left column = "351-561"
Right Column = "351-561-35C"
Result = Joined as equal.

我的解决方法是将字符串右填充到相同的长度。我不明白为什么这两个字符串会被视为相等。

语法中是否有某种机制可以改变这种行为？

D .

原文

I have created a query that uses a left join. For reasons I don't understand it will join two dissimilar length strings as if they are equal. An example would be:

Left column = "351-561"
Right Column = "351-561-35C"
Result = Joined as equal.

My workaround has been to right pad the strings to the same length. I don't understand though why those two strings would be treated as equal.

Is there some mechanism in the syntax that would change that behavior?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

自找没趣 2024-10-26 16:12:47

看起来您可能很需要在 Visual Fox Pro

回复收藏 0 原文

往日 2024-10-26 16:12:47

在 Visual FoxPro 中，= 运算符表示相等。 == 运算符的意思是“完全相等”。换句话说，== 运算符比较每个表达式的大小和值。例如：

* Returns .T. because the first 3 characters on the left match the right.
? "123" = "12345"

* Returns .F. because the expression sizes are not equal.
? "123" == "12345"

话虽如此，我认为 JOIN 失败的唯一原因是您要加入的列大小不同。例如，此查询仅返回 555-1234 记录，因为 CHAR 列会自动用空格填充。

CREATE CURSOR "MyLeft" (LeftPK I, LeftCode C(20))
INSERT INTO "MyLeft" VALUES(1, "351-561")
INSERT INTO "MyLeft" VALUES(2, "555-1234")

CREATE CURSOR "MyRight" (RightPK I, RightCode C(20))
INSERT INTO "MyRight" VALUES(1, "351-561-35C")
INSERT INTO "MyRight" VALUES(2, "555-1234")

SELECT MyLeft.*, MyRight.* FROM "MyLeft" JOIN "MyRight" ON LeftCode = RightCode

而此查询返回两条记录，因为列类型为 VARCHAR。

CREATE CURSOR "MyLeft" (LeftPK I, LeftCode V(20))
INSERT INTO "MyLeft" VALUES(1, "351-561")
INSERT INTO "MyLeft" VALUES(2, "555-1234")

CREATE CURSOR "MyRight" (RightPK I, RightCode V(20))
INSERT INTO "MyRight" VALUES(1, "351-561-35C")
INSERT INTO "MyRight" VALUES(2, "555-1234")

SELECT MyLeft.*, MyRight.* FROM "MyLeft" JOIN "MyRight" ON LeftCode = RightCode

In Visual FoxPro, the = operator means equal. The == operator means "exactly equal". In other words, the == operator compares the size and value of each expression. For example:

* Returns .T. because the first 3 characters on the left match the right.
? "123" = "12345"

* Returns .F. because the expression sizes are not equal.
? "123" == "12345"

That being said, the only reason I can think a JOIN would fail is because the columns you are joining on are not the same size. For example, this query only returns the 555-1234 record because a CHAR column is automatically padded right with spaces.

CREATE CURSOR "MyLeft" (LeftPK I, LeftCode C(20))
INSERT INTO "MyLeft" VALUES(1, "351-561")
INSERT INTO "MyLeft" VALUES(2, "555-1234")

CREATE CURSOR "MyRight" (RightPK I, RightCode C(20))
INSERT INTO "MyRight" VALUES(1, "351-561-35C")
INSERT INTO "MyRight" VALUES(2, "555-1234")

SELECT MyLeft.*, MyRight.* FROM "MyLeft" JOIN "MyRight" ON LeftCode = RightCode

Whereas this query returns both records because the column type is VARCHAR.

CREATE CURSOR "MyLeft" (LeftPK I, LeftCode V(20))
INSERT INTO "MyLeft" VALUES(1, "351-561")
INSERT INTO "MyLeft" VALUES(2, "555-1234")

CREATE CURSOR "MyRight" (RightPK I, RightCode V(20))
INSERT INTO "MyRight" VALUES(1, "351-561-35C")
INSERT INTO "MyRight" VALUES(2, "555-1234")

SELECT MyLeft.*, MyRight.* FROM "MyLeft" JOIN "MyRight" ON LeftCode = RightCode

回复收藏 0 原文

~没有更多了~