mysql两个表left join_mysql中两张表使用left join on 求差集

1.表结构

mysql> select * from allStudents;

+----+-------+

| id | name |

+----+-------+

| 1 | ???? |

| 2 | ???? |

| 3 | ???·

| 4 | four |

+----+-------+

4 rows in set (0.00 sec)

mysql> select * from currentStudents;

+----+--------+

| id | name |

+----+--------+

| 1 | luowen |

| 3 | 毛毛想 |

+----+--------+

2.子查询方法

mysql> select * from test where test.id not in ( select id from user);

+----+----------+--------+

| id | name | salary |

+----+----------+--------+

| 2 | 脙芦脙芦 | 4000 |

| 4 | four | 23232 |

+----+----------+--------+

3.left join 方法

mysql> select allStudents.*,currentStudents.* from allStudents,currentStudents where allStudents.id = currentStudents.id;

+----+-------+----+---------+

| id | name | id | name |

+----+-------+----+---------+

| 1 | ???? | 1 | luowen |

| 3 | ???· | 3 | 毛毛想 |

+----+-------+----+---------+

2 rows in set (0.00 sec)

mysql> select allStudents.*,currentStudents.* from allStudents left join currentStudents on allStudents.id = currentStudents.id;

+----+-------+------+------------+

| id | name | id | name |

+----+-------+------+------------+

| 1 | ???? | 1 | luowen |

| 2 | ???? | NULL | NULL |

| 3 | ???· | 3 | 毛毛想 |

| 4 | four | NULL | NULL |

+----+-------++------+-----------+

4 rows in set (0.00 sec)

mysql> select allStudents.*,currentStudents.* from allStudents left join currentStudents on allStudents.id = currentStudents.id where currentStudents.id is null;

+----+------+------+----------+

| id | name | id | name |

+----+------+------+----------+

| 2 | ???? | NULL | NULL |

| 4 | four | NULL | NULL |

+----+------+------+----------+

2 rows in set (0.00 sec)