JOIN returns no rows and LEFT JOIN returns all values as null-CodePudding

I'm pretty new to SQL and mostly dealt with databases involved in logins, basic data.

I'm trying to join 2 tables and am having trouble.

I have 2 tables, one is a complete list of all UK lsoa numbers and info on postcode called postcode, and a secondary table that contains information about the deprivation index of an area which also contains the lsoa number called depriv.

I'm trying to join these tables via the lsoa which is a VARCHAR with a format like: E01000005

My SQL is:

SELECT a.lsoa11, b.decile FROM postcode a
JOIN depriv b ON a.lsoa11=b.lsoa

This returns nothing, empty table

but:

SELECT a.lsoa11, b.decile FROM postcode a
LEFT JOIN depriv b ON a.lsoa11=b.lsoa

Returns the lsoa11 table column correctly but the decile column is just (NULL)

What am I doing wrong?

SQL
|
|-postcode
|   |-lsoa11 { 'E01000001'; 'E01000002'; 'E01000003' }
|   |-pcd { 'EH1 1GF'; 'EH1 1GP'; 'EH1 5GP' }
|-depriv
|   |-lsao { 'E01000001'; 'E01000002'; 'E01000003' }
|   |-rank { '24,004'; '24,201'; '12,102' }

CodePudding user response：

JOIN means INNER JOIN or CROSS JOIN in MySQL. It links data between one or more tables based on values of the common column between the tables.

LEFT JOIN selects all data starting from the left table. For each row in the left table, it compares with every row in the right table and creating a corresponding new row.

Therefore, it can be inferred that postcode and depriv have no commons in lsoa which leads to such result.