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)
