mysql联合查询

班级表:
CREATE TABLE `join_test_class` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

学生表:
CREATE TABLE `join_test_student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(15) DEFAULT NULL,
  `cla_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

• 查询某个学生的班级

mysql> select a.*,b.* from join_test_student as a left join join_test_class as b on a.cla_id = b.id where a.id = 2;
+----+-------+--------+----+---------+
| id | name  | cla_id | id | name    |
+----+-------+--------+----+---------+
|  2 | name2 |      2 |  2 | 班级2   |
+----+-------+--------+----+---------+
1 row in set (0.00 sec)

• 查询某个班级的学生信息

mysql> select a.*,b.* from join_test_student as a left join join_test_class as b on a.cla_id = b.id where b.id = 3;
+----+-------+--------+----+---------+
| id | name  | cla_id | id | name    |
+----+-------+--------+----+---------+
|  3 | name3 |      3 |  3 | 班级3   |
+----+-------+--------+----+---------+
1 row in set (0.00 sec)

• 查询所有学生里对应的班级cla_id无效

mysql> select a.*,b.* from join_test_student as a left join join_test_class as b on a.cla_id = b.id where b.id is null;
+----+-------+--------+------+------+
| id | name  | cla_id | id   | name |
+----+-------+--------+------+------+
|  4 | name4 |      4 | NULL | NULL |
+----+-------+--------+------+------+
1 row in set (0.00 sec)

• 查询所有班级里，没有学生的班级

mysql> select a.*,b.* from join_test_student as a right join join_test_class as b on a.cla_id = b.id where a.id is null;
+------+------+--------+----+---------+
| id   | name | cla_id | id | name    |
+------+------+--------+----+---------+
| NULL | NULL |   NULL |  1 | 班级1   |
+------+------+--------+----+---------+
1 row in set (0.00 sec)