It's because of the WHERE
clause.
Left join sets all columns of TableB
to NULL for those rows that don't exist in TableA
. This implies that the comparison where b.userid = 1
is not true, that is why those rows are not being returned.
When you specify a column value from the right table in a WHERE
clause, you necessarily eliminate all of the NULL
values and it essentially becomes an INNER JOIN
.
Try this: on a.mid=b.mid and b.userid=1
Best regards,
Cosmog Hong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".