why does INNER JOIN return empty set on two tables containing matching data?

Question

My MySQL returns empty set when I join on a common value UPC. I can do a left join and see the same UPC value on the right side when I do a right join. Why does an inner join return empty set then???? How do I make the join work??

MySQL>

select
    upc_descriptions.UPC,
    left(upc_descriptions.Description,15) as Description,
    product_upc.ProductCode, product_upc.UPC
from upc_descriptions
left join product_upc
    ON upc_descriptions.UPC=product_upc.UPC
where upc_descriptions.UPC = 009326204994;

| UPC          | Description     | ProductCode | UPC  |
+--------------+-----------------+-------------+------+
| 009326204994 | KWIKSET PRO ADJ | NULL        | NULL |
| 009326204994 | KWIKSET PRO ADJ | NULL        | NULL |
+--------------+-----------------+-------------+------+
2 rows in set (0.04 sec)

MySQL>

select
    upc_descriptions.UPC,
    upc_descriptions.Description,
    product_upc.ProductCode,
    product_upc.UPC
from upc_descriptions
right join product_upc
    ON upc_descriptions.UPC=product_upc.UPC
where product_upc.UPC = 009326204994;


+------+-------------+-------------+-----------------+
| UPC  | Description | ProductCode | UPC             |
+------+-------------+-------------+-----------------+
|NULL | NULL        | 239070      | 009326204994
|NULL | NULL        | 239070      | 00009326204994
+------+-------------+-------------+-----------------+
2 rows in set, 2 warnings (0.03 sec)

MySQL>

select
    upc_descriptions.UPC,
    upc_descriptions.Description,
    product_upc.ProductCode,
    product_upc.UPC
from upc_descriptions
inner join product_upc
    ON upc_descriptions.UPC = product_upc.UPC
where product_upc.UPC = 009326204994;
Empty set (0.03 sec)

Table definitions:

mysql> describe upc_descriptions;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| UPC         | varchar(20)  | NO   |     | NULL    |                |
| Description | varchar(256) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe product_upc;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| ProductCode | varchar(50) | NO   |     | NULL    |                |
| UPC         | varchar(20) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Here's the values that were loaded into the table.

cat productCode-UPC_sorted.csv | grep 009326204994
239070,009326204994
239070,00009326204994

kid927 suggested casting as SIGNED when defining the index tables to join. At first I thought this was going to work really well, but then as I dug deeper I ran into another strange problem. Here's the query and result:

mysql>

select 
    upc_descriptions.UPC,left(upc_descriptions.Description,15) as Description, 
    product_upc.ProductCode, product_upc.UPC 
from upc_descriptions left join product_upc 
    ON CAST(upc_descriptions.UPC as SIGNED)=CAST(product_upc.UPC as SIGNED)
where upc_descriptions.UPC = 009326204994;

| UPC          | Description     | ProductCode | UPC             |
+--------------+-----------------+-------------+-----------------+
|9326204994    | KWIKSET PRO ADJ | 239070      | 009326204994
|9326204994    | KWIKSET PRO ADJ | 239070      | 009326204994
|009326204994  | KWIKSET PRO ADJ | 239070      | 00009326204994
|009326204994  | KWIKSET PRO ADJ | 239070      | 00009326204994
+--------------+-----------------+-------------+-----------------+
4 rows in set, 65535 warnings (0.07 sec)

Where things get really weird is when I remove the WHERE clause and just look at the data that is joined. I get strange results where mysql matches one string to a substring of the other table producing a wrong result. This only makes sense if it is doing a CHAR type string comparison, but when I explicitly cast the UPC data as a SIGNED int, it should be comparing numbers and not doing sub-string comparisons... so I'm really confused about what's going on here. Here's an example:

mysql>

SELECT 
    upc_descriptions.UPC,
    left(upc_descriptions.Description,15) as Description,
    product_upc.ProductCode, 
    product_upc.UPC 
FRP, upc_descriptions INNER JOIN product_upc 
    ON CAST(upc_descriptions.UPC as SIGNED)=CAST(product_upc.UPC as SIGNED);

| UPC          | Description     | ProductCode | UPC             |
+--------------+-----------------+-------------+-----------------+
| 4036280125   | PASS PRO SERIES | 21096285    | 694036280125
| 4036960027   | KWIKSET PRO SIN | 21099030    | 694036960027
+--------------+-----------------+-------------+-----------------+
913 rows in set, 65535 warnings (21.33 sec)

See how the UPC in the left table (4036280125) returns a match to the right table where it is a substring in the number (694036280125). This looks like a string/char match despite the fact that I cast it as SIGNED. Further, this is wrong, because the number in the right table (694036280125) refers to a completely different product.

It is text because UPC number is nominal data. The number it represents is a label not meant to do math on it. Without the leading 00's it will not refer back to the original data, like clipping the letters off a first name... Mike -> ke ... would ruin any database of names... — Mike, Jan 15 '19 at 05:43
Is there a way to make it refer to the data as strings instead of numbers?? — Mike, Jan 15 '19 at 05:44
You should decide whether the field is really a number or text. If it's text, then compare it to string literals. If a number, then compare to a number. Don't do both at the same time. — Tim Biegeleisen, Jan 15 '19 at 05:48
Well it should be a string. How do I do it with string literals??? — Mike, Jan 15 '19 at 05:49

Nick · Answer 1 · 2019-01-15T06:38:28.210

The reason you get no results is because the columns you are joining on are both strings. When you compare them to an integer, MySQL does type conversion of string to integer, and in that case the values are equal. However when they are compared as strings they are not. Try this:

SELECT '01' = 1, '1' = 01, '01' = '1'

Output:

'01' = 1    '1' = 01    '01' = '1'
1           1           0

There are ways around this. You could cast your values as numbers, or trim leading zeros off the values:

SELECT CAST('01' AS UNSIGNED) = CAST('1' AS UNSIGNED)
SELECT TRIM(LEADING '0' FROM '01') = TRIM(LEADING '0' FROM '1')

Demo on dbfiddle

Here is a demo on dbfiddle of your query working fine using TRIM.

@Mike I've added a demo of your query using `TRIM` which works in a small example. — Nick, Jan 15 '19 at 06:39

score 0 · Answer 2 · answered Jan 15 '19 at 05:03

0

You can try below - you can see that your UPC codes are not same in two tables - one table has leading 00 before the code and that's why it is not showing for inner join

    select upc_descriptions.UPC, upc_descriptions.Description, product_upc.ProductCode, product_upc.UPC 
    from upc_descriptions inner join product_upc 
    ON upc_descriptions.UPC=concat('00',product_upc.UPC)
    where product_upc.UPC='009326204994';

answered Jan 15 '19 at 05:03

Fahmi

37,315
5
22
31

that was my initial thought... but you see it returns the truncated result in the right join when I search using the whole number with leading 0's... – Mike Jan 15 '19 at 05:08
what is your datatype for UPC in both table @Mike – Fahmi Jan 15 '19 at 05:09
to confound things, I loaded the data into the table with leading zeros... but mysql truncated it... despite both tables being defined the same for UPC... – Mike Jan 15 '19 at 05:09
@Mike, I guess it because of your datatype – Fahmi Jan 15 '19 at 05:10

score 0 · Answer 3 · answered Jan 15 '19 at 05:39

0

Nick beat me to it but I tried with CAST(UPC as SIGNED) and CAST(UPC as UNSIGNED) and it worked:

select upc_descriptions.UPC,left(upc_descriptions.Description,15) as Description, product_upc.ProductCode, product_upc.UPC from upc_descriptions left join product_upc ON CAST(upc_descriptions.UPC as SIGNED)=CAST(product_upc.UPC as SIGNED) where upc_descriptions.UPC = 009326204994;

I refer SIGNED and UNSIGNED explanation in this post.

answered Jan 15 '19 at 05:39

FanoFN

6,815
2
13
33

oh wait... doing it as number is having some really weird results and causing 65000 errors and missing 20,000 expected records... – Mike Jan 15 '19 at 05:56
it's causing matches where there shouldn't be like this: |4036501152 | | 21091049 | 694036501152 see it matches 4036501152 to 694036501152 because it contains only some of the number... but those two shouldn't match at all.... ??? – Mike Jan 15 '19 at 06:07
oh wow.. I was only working with one or two rows while I execute that syntax. I'll try again using bigger data and update if I could get it working @Mike – FanoFN Jan 15 '19 at 06:55
Can you please post the query that get the mismatched result? – FanoFN Jan 15 '19 at 06:58
I just copy and pasted your query above using cast to make the UPC signed. – Mike Jan 15 '19 at 12:45
select upc_descriptions.UPC,left(upc_descriptions.Description,15) as Description, product_upc.ProductCode, product_upc.UPC from upc_descriptions left join product_upc ON CAST(upc_descriptions.UPC as CHAR)=CAST(product_upc.UPC as CHAR); – Mike Jan 15 '19 at 14:57
I edited the original post to show the additional query and the mismatched data. – Mike Jan 15 '19 at 17:41
This is strange @Mike .. I tried inserted the data you retrieved and run the exact syntax but doesn't produce a result like that. I think I couldn't help much as beyond this point, sorry mike. I hope someone would give you a working solution, I am also eager to know the details of what went wrong. – FanoFN Jan 16 '19 at 03:33

why does INNER JOIN return empty set on two tables containing matching data?

3 Answers3