Mysql: String comparison using = operator

Question

I'm new to mysql and I'm learning join queries now. when I compare strings I got weird output that mentioned below. I have two tables

   MariaDB [test]> select * from classroom;
    +---------+-----------+
    | subject | classroom |
    +---------+-----------+
    | maths   |         1 |
    | englishs|         2 |
    +---------+-----------+

Table student:

  MariaDB [test]> select * from student;
  +------+------+---------+
  | id   | name | subject |
  +------+------+---------+
  |    1 | abc  | maths   |
  |    2 | abcd | english |
  +------+------+---------+

I have tried this query

 select b.classroom,a.name,b.subject from student a left join classroom b
 on a.subject = b.subject ;

and the output is like,

  +-----------+------+---------+
  | classroom | name | subject |
  +-----------+------+---------+
  |         1 | abc  | maths   |
  |      NULL | abcd | NULL    |
  +-----------+------+---------+

I don't understand why am getting second row if the strings are doesn't match in both tables.

Answer 1

This has nothing to do with string comparison.

You are using an outer join, but the result you are expecting is the one that inner joins gives.

Take a look at this post for a good explanation about inner and outer joins.

From that post:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

• An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Answer 2

try this may be this will work.

 select b.classroom,a.name,b.subject from student a,classroom b where a.subject = b.subject