Is it possible to
FROM master_listing AS a
LEFT JOIN courier_information AS b ON LEFT(a.remarks, 6) = b.courierCode
id courierCode UID
1 112233 27
A NULL value would be the expected return for
b.anycolumn from the
a LEFT JOIN b operation, when there is no "matching" row found in
b. (The rows from
a will be returned, and MySQL will return NULL values for all columns from
It looks like you are asking why the row from
b isn't being matched; why the comparison test isn't returning TRUE for any rows.
What you've omitted is the datatype of the
Given that a query with the condition
a.remarks = b.courierCode does find a matching row, and given that we are shown example value in the
remarks column as
'112233GOODAY', we surmise that the
remarks column is character type.
We can also conclude that the comparison is not on character string values.
Evaluated in a numeric context, the string value
'112233GOODAY' is going to be interpreted as numeric value, of
112233. So we know that there is an implicit datatype conversion occurring, the
remarks is being converted to numeric. That same conversion should be happening on the return from the
CREATE TABLE master_listing ( id INT UNSIGNED PRIMARY KEY , remarks VARCHAR(12) ); INSERT INTO master_listing (id, remarks) VALUES (1,'112233GOODAY'); CREATE TABLE courier_information ( id INT UNSIGNED PRIMARY KEY , couriercode INT UNSIGNED , uid INT UNSIGNED ); INSERT INTO courier_information (id, couriercode, uid) VALUES (1,112233,27);
SELECT b.UID FROM master_listing a LEFT JOIN courier_information b ON LEFT(a.remarks, 6) = b.courierCode ;
Output as expected:
UID ------ 27
There's something going on that you haven't revealed. I've made assumptions about the datatypes of the columns.
My guess (just a guess) is that there is a leading space in the
For debugging this, output additional columns to inspect the values...
SELECT a.id , a.remarks , LEFT(a.remarks,6) , HEX(a.remarks) FROM master_listing a WHERE a.id = 1
remarks value has a leading space, then the
LEFT(remarks,6) will evaluate to
' 11223', and compared in a numeric context, that is not equal to