mysql左右外连接

### 先创建两张表
mysql> create table stu(
    -> id int,
    -> name varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into stu values(1,"jack"),(2,"tom"),(3,"kity"),(4,"nono");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table exam(
    -> id int,
    -> grade int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into exam values(1,56),(2,76),(11,8);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

select * from stu;            select * from exam;

+------+------+               +------+-------+
| id   | name |               | id   | grade |
+------+------+               +------+-------+
|    1 | jack |               |    1 |    56 |
|    2 | tom  |               |    2 |    76 |
|    3 | kity |               |   11 |     8 |
|    4 | nono |               +------+-------+
+------+------+               

### 若不使用左右外连接
mysql> select name ,stu.id ,grade from stu,exam where stu.id=exam.id;
+------+------+-------+
| name | id   | grade |
+------+------+-------+
| jack |    1 |    56 |
| tom  |    2 |    76 |
+------+------+-------+

### 使用左连接(左侧表完全显示)
### 显示所有人成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空
mysql> select name ,stu.id ,grade from stu left join exam on stu.id=exam.id;
+------+------+-------+
| name | id   | grade |
+------+------+-------+
| jack |    1 |    56 |
| tom  |    2 |    76 |
| kity |    3 |  NULL |
| nono |    4 |  NULL |
+------+------+-------+

### 使用右连接
mysql> select name ,stu.id ,grade from stu right join exam on stu.id=exam.id;
+------+------+-------+
| name | id   | grade |
+------+------+-------+
| jack |    1 |    56 |
| tom  |    2 |    76 |
| NULL | NULL |     8 |
+------+------+-------+