MySQL connection returns no results even if it should
I have a seemingly straightforward problem. I am mapping a list of ids to another list of ids in a second table. The following queries run correctly and return a result:
select * from dataTable where id = 3888;
select * from moreIDs where mmid = 3888;
However, the union operator does not return any results.
select * from dataTable inner join moreIDs on dataTable.id = moreIDs.mmID;
Any idea why I am not getting any results from joining?
source to share
As you understood int comments, your problem is with data types.
The following fiddle shows some tests: fiddle .
First, I created three tables like this:
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 ask for a column varchar
by comparing it to a value int
, it varchar
will be implanted, appended to int
, and extra spaces 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 an operation JOIN
, you will be comparing varchar
with varchar
, so it '3888' = ' 3888 '
will evaluate to false.
To solve this problem, you can convert one of the columns to int
(this is how cast will be used) or use a function TRIM()
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 . Casting ( varchar
- int
) on each row will have a performance impact if you are using indexes.
source to share