how to use left join in mysql

Let's setup two tables for our experiment.

create table student(id int primary key auto_increment, name varchar(255));

insert into student (name) values('henry'),('jack'),('alice');

create table class_a(id int primary key auto_increment, student_id int, registered_datetime datetime);

insert into class_a(student_id, registered_datetime) values(1,now());

We use a simple left join to see the result:

select s.*,c.id as class_id from student s left join class_a c on s.id = c.student_id;
+----+-------+----------+
| id | name  | class_id |
+----+-------+----------+
|  1 | henry |        1 | 
|  2 | jack  |     NULL |
|  3 | alice |     NULL |
+----+-------+----------+

We can see that left join returns all rows from the left table(student) even the student may not have a match in the right table(class_a). In this case, any c

So thing are quite obvious now. If we only want to get the students that are not registered in class_a, we can simply add a filter for left join:

select s.*,c.id as class_id from student s left join class_a c on s.id = c.student_id where c.id is null;
+----+-------+----------+
| id | name  | class_id |
+----+-------+----------+
|  2 | jack  |     NULL |
|  3 | alice |     NULL |
+----+-------+----------+