mysql中两张表使用left join on 求差集详解数据库

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 | 毛毛想 | 
+----+--------+ 

mysql> select * from test where test.id not in ( select id from user); 
+----+----------+--------+ 
| id | name     | salary | 
+----+----------+--------+ 
|  2 | 脙芦脙芦     |   4000 | 
|  4 | four     |  23232 | 
+----+----------+--------+ 

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)