MySQL join doesn't return results even when it should

Question

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?

What is the data type of these columns are they int or numeric — radar, Oct 17 '14 at 21:06
Are you sure that any values actually match? Just because each has 3888 doesn't mean they are exact matches.... — KathyA., Oct 17 '14 at 21:06
KathyA, can you elaborate? I'm not quite sure what you mean. — Anne, Oct 17 '14 at 21:08
Actually, I think I figured it out. I'm not sure why, but when I changed the type of the moreIds' mmID to be an integer, it works. I'm reading the file in from excel. Any ideas what has happened? — Anne, Oct 17 '14 at 21:10
I would guess that you have trailing spaces or extra characters in your varchar. For instance at http://www.sqlfiddle.com/#!2/5100d/1 when I look for id = 3888, it returns both records from dataTable, even though the second record is actually '3888 ' — Brian Hoover, Oct 17 '14 at 21:12
Thanks Brian! I took a closer look at my data files, and it appears you're correct. — Anne, Oct 17 '14 at 21:22

score 2 · Accepted Answer · edited May 23 '17 at 12:04

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.

MySQL join doesn't return results even when it should

1 Answers1