Why does SQL Join find two dissimilar length strings equal?

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 len开发者_开发知识库gth. 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?

D.

It looks like you may well need to use the == operator in Visual Fox Pro

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