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)

　　

转载于:https://www.cnblogs.com/luowen/p/3548719.html