SQL表连接查询

两张表：

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 小王 |   16 |
|  2 | 小红 |   18 |
|  3 | 小明 |   20 |
|  4 | 小李 |   22 |
|  5 | 小强 |   17 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> select * from mark;
+----+------+-----------+
| id | mark | studentid |
+----+------+-----------+
|  1 |   61 |         2 |
|  2 |   45 |         2 |
|  3 |   70 |         3 |
+----+------+-----------+
3 rows in set (0.00 sec)

一、内连接（inner join)

mysql> select s.name,m.mark from student s,mark m where s.id=m.studentid;
+------+------+
| name | mark |
+------+------+
| 小红 |   61 |
| 小红 |   45 |
| 小明 |   70 |
+------+------+
3 rows in set (0.07 sec)
mysql> select s.name,m.mark from student s inner join mark m on s.id=m.studentid;
+------+------+
| name | mark |
+------+------+
| 小红 |   61 |
| 小红 |   45 |
| 小明 |   70 |
+------+------+
3 rows in set (0.06 sec)

二、左连接（left join)

mysql> select s.name,m.mark from student s left join mark m on s.id=m.studentid;
+------+------+
| name | mark |
+------+------+
| 小红 |   61 |
| 小红 |   45 |
| 小明 |   70 |
| 小王 | NULL |
| 小李 | NULL |
| 小强 | NULL |
+------+------+
6 rows in set (0.07 sec)

三、右连接（right join)

mysql> select s.name,m.mark from student s right join mark m on s.id=m.studentid;
+------+------+
| name | mark |
+------+------+
| 小红 |   61 |
| 小红 |   45 |
| 小明 |   70 |
+------+------+
3 rows in set (0.00 sec)