mysql之 左连接 右连接 内连接

本篇文章记录下关于mysql 左右连接、内连接的用法。
例子里用到两张表

• student 表

mysql> select * from student;
+----+--------+-----+----------+
| id | name   | age | city     |
+----+--------+-----+----------+
|  1 | sallai |  11 | shanghai |
|  2 | jone   |  18 | beijing  |
|  3 | tony   |  19 | tianjin  |
|  4 | lisa   |  20 | liaoning |
+----+--------+-----+----------+
4 rows in set (0.00 sec)

• memory 表

mysql> select * from memory;
+----+----------+--------+
| id | name     | memory |
+----+----------+--------+
|  1 | lisi     |  15000 |
|  2 | wangwu   |  36000 |
|  3 | sallai   |  52200 |
|  4 | xiaoming |  36000 |
+----+----------+--------+
4 rows in set (0.00 sec)

1.左连接

左连接就是以左边表为全数据，匹配右表符合条件的记录。

mysql> select * from student a left join memory b on a.name = b.name;
+----+--------+-----+----------+------+--------+--------+
| id | name   | age | city     | id   | name   | memory |
+----+--------+-----+----------+------+--------+--------+
|  1 | sallai |  11 | shanghai |    3 | sallai |  52200 |
|  2 | jone   |  18 | beijing  | NULL | NULL   |   NULL |
|  3 | tony   |  19 | tianjin  | NULL | NULL   |   NULL |
|  4 | lisa   |  20 | liaoning | NULL | NULL   |   NULL |
+----+--------+-----+----------+------+--------+--------+
4 rows in set (0.00 sec)

2.右连接

右连接就是以右边表为全数据，匹配左表符合条件的记录。

mysql> select * from student a right join memory b on a.name = b.name;
+------+--------+------+----------+----+----------+--------+
| id   | name   | age  | city     | id | name     | memory |
+------+--------+------+----------+----+----------+--------+
| NULL | NULL   | NULL | NULL     |  1 | lisi     |  15000 |
| NULL | NULL   | NULL | NULL     |  2 | wangwu   |  36000 |
|    1 | sallai |   11 | shanghai |  3 | sallai   |  52200 |
| NULL | NULL   | NULL | NULL     |  4 | xiaoming |  36000 |
+------+--------+------+----------+----+----------+--------+
4 rows in set (0.00 sec)

3.内连接

很显然这条语句和返回了符合条件的a表和b表的记录
select * from student a inner join memory b on a.name = b.name;
select * from student a student b where a.name = b.name;
从结果上看这两条语句等效

mysql> select * from student a inner join memory b on a.name = b.name;
+----+--------+-----+----------+----+--------+--------+
| id | name   | age | city     | id | name   | memory |
+----+--------+-----+----------+----+--------+--------+
|  1 | sallai |  11 | shanghai |  3 | sallai |  52200 |
+----+--------+-----+----------+----+--------+--------+
1 row in set (0.00 sec)

---

mysql> select * from student a,memory b where a.name = b.name;
+----+--------+-----+----------+----+--------+--------+
| id | name   | age | city     | id | name   | memory |
+----+--------+-----+----------+----+--------+--------+
|  1 | sallai |  11 | shanghai |  3 | sallai |  52200 |
+----+--------+-----+----------+----+--------+--------+
1 row in set (0.00 sec)