CODEWITHSUNDEEP
mysqlsqljoin
Anne
Anne

Reputation: 261

MySQL join doesn't return results even when it should

I have a seemingly straight forward problem. I'm matching a list of ids with another list of ids in a second table. The following queries execute fine and return a result:

select * from dataTable where id = 3888;
select * from moreIDs where mmid = 3888;

The join statement, however, returns no results.

select * from dataTable inner join moreIDs on dataTable.id = moreIDs.mmID;

Any idea why I'm not getting any results from the join?

Upvotes: 3

Views: 435

Answers (1)

Zanon
Zanon

Reputation: 30800

As you've figured out int the comments, your issue is related with data types.

The following fiddle shows some tests: fiddle.

First, I've created three tables as the following:

CREATE TABLE table1 (id varchar(15));
CREATE TABLE table2 (id varchar(15));
CREATE TABLE table3 (id int);


And inserted some data: 

INSERT INTO table1 values ('3888');
INSERT INTO table2 values (' 3888 '); -- extra spaces
INSERT INTO table3 values (3888);


If you query a varchar column comparing it with an int value, the varchar will be implicity cast to int and extra spaces will be removed. The following examples return 3888:

SELECT * FROM table1 WHERE id = 3888; 
SELECT * FROM table2 WHERE id = 3888;


But if you try this match in a JOIN operation, you will be comparing varchar with varchar, so '3888' = ' 3888 ' will be evaluated as false.

To solve this, you may convert one of the columns to int (so cast will be used) or use the TRIM() function, like:

SELECT * 
FROM table1
INNER JOIN table2
ON TRIM(table1.id) = TRIM(table2.id);


Note: If possible, convert both columns to int to get a SARGABLE query. The cast operation (varchar to int) in each row will have a performance impact if you use indexes.

Upvotes: 2

Related Questions