MySQL Left Join Query Record Count

Recently fixed a data issue that involved left joins. In my mind, I thought that A left join B would result in the number of records being the same as table A’s record count. However, after actual operation, I discovered my understanding was seriously wrong.

A Diagram About Left Joins from the Internet

Taken from a diagram online, the diagram itself is correct, but I misunderstood it. I thought the record count would still be the same as table A’s record count.

Here’s an example to illustrate the problem

An Example

student table data as follows

id,name,address
1,stu_1,beijing
3,stu_3,dalian
22,alan,wuhan
55,alan,xinjiang

address table records as follows

address,id,description
xinjiang,1,xinjiang真是美
xinjiang,2,xinjiang真是大

Left join operation as follows

select *
from student a
         left join address b on a.address = b.address

The final result set record count is 5. Notice that there are two records for address xinjiang. WHY? Because address xinjiang indeed has two records in the address table.

Conclusion

W3Schools says this about left joins:

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

This statement should be improved to clarify that all matching records from table2 will be displayed.

For the final left join record count, it must be >= table1’s record count, because even if no related records are found in table2 for the join condition, they still need to be displayed. But if matches are found and they are not unique, then all these non-unique multiple records will be combined and displayed.

Of course, for right joins and inner joins, the results are similar when there are multiple matching conditions.

Final Thoughts

Although the problem is simple, it still needs attention.