mysql内连接 外连接 自然连接 全连接
# 查看两张表中的数据
mysql> select * from student;
+------+-------+------+------+----------+---------+
| no | name | age | sex | schoolid | classid |
+------+-------+------+------+----------+---------+
| 1 | Ada | 21 | 女 | 1 | 1 |
| 2 | Bob | 22 | 男 | 2 | 2 |
| 3 | Clair | 21 | 女 | 3 | 3 |
| NULL | David | 19 | 男 | NULL | NULL |
+------+-------+------+------+----------+---------+
4 rows in set (0.00 sec)
mysql> select * from school;
+----+------------------+
| no | name |
+----+------------------+
| 1 | 长春大学 |
| 2 | 长春理工大学 |
| 3 | 吉林大学 |
| 4 | 吉林师范大学 |
| 5 | 长春大学旅游学院 |
| 6 | NULL |
+----+------------------+
6 rows in set (0.00 sec)
首先介绍一下笛卡尔积,就是一张表中的每一行与另一张表中的每一行组合为一行
mysql> select * from student,school;
+------+-------+------+------+----------+---------+----------+--------------+
| no | name | age | sex | schoolid | classid | schoolid | name |
+------+-------+------+------+----------+---------+----------+--------------+
| 1 | Ada | 21 | 女 | 1 | 1 | 1 | 长春大学 |
| 2 | Bob | 22 | 男 | 2 | 2 | 1 | 长春大学 |
| 3 | Clair | 21 | 女 | 3 | 3 | 1 | 长春大学 |
| NULL | David | 19 | 男 | NULL | NULL | 1 | 长春大学 |
| 1 | Ada | 21 | 女 | 1 | 1 | 2 | 长春理工大学 |
| 2 | Bob | 22 | 男 | 2 | 2 | 2 | 长春理工大学 |
| 3 | Clair | 21 | 女 | 3 | 3 | 2 | 长春理工大学 |
| NULL | David | 19 | 男 | NULL | NULL | 2 | 长春理工大学 |
| 1 | Ada | 21 | 女 | 1 | 1 | 3 | 吉林师范大学 |
| 2 | Bob | 22 | 男 | 2 | 2 | 3 | 吉林师范大学 |
| 3 | Clair | 21 | 女 | 3 | 3 | 3 | 吉林师范大学 |
| NULL | David | 19 | 男 | NULL | NULL | 3 | 吉林师范大学 |
| 1 | Ada | 21 | 女 | 1 | 1 | 4 | 吉林大学 |
| 2 | Bob | 22 | 男 | 2 | 2 | 4 | 吉林大学 |
| 3 | Clair | 21 | 女 | 3 | 3 | 4 | 吉林大学 |
| NULL | David | 19 | 男 | NULL | NULL | 4 | 吉林大学 |
| 1 | Ada | 21 | 女 | 1 | 1 | 5 | NULL |
| 2 | Bob | 22 | 男 | 2 | 2 | 5 | NULL |
| 3 | Clair | 21 | 女 | 3 | 3 | 5 | NULL |
| NULL | David | 19 | 男 | NULL | NULL | 5 | NULL |
+------+-------+------+------+----------+---------+----------+--------------+
20 rows in set (0.00 sec)
内连接查询
inner join或cross join 或join ,在mysql中这三种方式都可以实现内连接,是等效的,但不要认为这三者在标准sql语句中等效.
使用内连接可以查询出学生及所在学校的信息,但是未指定学校的学生和没有学生的学校不会出现在结果中
mysql> select * from student join school on student.schoolid=school.no;
+------+-------+------+------+----------+---------+----+--------------+
| no | name | age | sex | schoolid | classid | no | name |
+------+-------+------+------+----------+---------+----+--------------+
| 1 | Ada | 21 | 女 | 1 | 1 | 1 | 长春大学 |
| 2 | Bob | 22 | 男 | 2 | 2 | 2 | 长春理工大学 |
| 3 | Clair | 21 | 女 | 3 | 3 | 3 | 吉林大学 |
+------+-------+------+------+----------+---------+----+--------------+
3 rows in set (0.00 sec)
外连接
左外连接
使用left outer join 或 left join
较内连接,左外连接中左面的表中未指定关联信息的行(比如未指定学校的学生)会得到展示
mysql> select * from student left outer join school on student.schoolid=school.no;
+------+-------+------+------+----------+---------+------+--------------+
| no | name | age | sex | schoolid | classid | no | name |
+------+-------+------+------+----------+---------+------+--------------+
| 1 | Ada | 21 | 女 | 1 | 1 | 1 | 长春大学 |
| 2 | Bob | 22 | 男 | 2 | 2 | 2 | 长春理工大学 |
| 3 | Clair | 21 | 女 | 3 | 3 | 3 | 吉林大学 |
| NULL | David | 19 | 男 | NULL | NULL | NULL | NULL |
+------+-------+------+------+----------+---------+------+--------------+
4 rows in set (0.00 sec)
右外连接
right outer join或right join
较内连接,右外连接中右面的表中未指定关联信息的行(这里是没有学生的学校)会被展示
mysql> select * from student right outer join school on student.schoolid=school.no;
+------+-------+------+------+----------+---------+----+------------------+
| no | name | age | sex | schoolid | classid | no | name |
+------+-------+------+------+----------+---------+----+------------------+
| 1 | Ada | 21 | 女 | 1 | 1 | 1 | 长春大学 |
| 2 | Bob | 22 | 男 | 2 | 2 | 2 | 长春理工大学 |
| 3 | Clair | 21 | 女 | 3 | 3 | 3 | 吉林大学 |
| NULL | NULL | NULL | NULL | NULL | NULL | 4 | 吉林师范大学 |
| NULL | NULL | NULL | NULL | NULL | NULL | 5 | 长春大学旅游学院 |
| NULL | NULL | NULL | NULL | NULL | NULL | 6 | NULL |
+------+-------+------+------+----------+---------+----+------------------+
6 rows in set (0.00 sec)
全外连接
mysql 不支持
full outer join
mysql> select * from student full outer join school on student.schoolid=school.no;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join school on student.schoolid=school.no' at line 1
但可以通过union连接左外连接和右外连接实现全外连接查询
mysql> select * from student left join school on student.schoolid=school.no
-> union
-> select * from student right join school on student.schoolid=school.no;
+------+-------+------+------+----------+---------+------+------------------+
| no | name | age | sex | schoolid | classid | no | name |
+------+-------+------+------+----------+---------+------+------------------+
| 1 | Ada | 21 | 女 | 1 | 1 | 1 | 长春大学 |
| 2 | Bob | 22 | 男 | 2 | 2 | 2 | 长春理工大学 |
| 3 | Clair | 21 | 女 | 3 | 3 | 3 | 吉林大学 |
| NULL | David | 19 | 男 | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 4 | 吉林师范大学 |
| NULL | NULL | NULL | NULL | NULL | NULL | 5 | 长春大学旅游学院 |
| NULL | NULL | NULL | NULL | NULL | NULL | 6 | NULL |
+------+-------+------+------+----------+---------+------+------------------+
7 rows in set (0.00 sec)
自然连接
mysql> select * from student natural join school;
Empty set (0.00 sec)
mysql> select * from student natural join school on student.schoolid = school.no;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on student.schoolid = school.no' at line 1
以上方式是查询不到自然连接结果的,需要有相同名称的列才可以,而且不用指定关联的列,自然连接会自动关联相同列名的列
新建两张表
mysql> select * from one;
+--------+----------+--------+
| one_id | one_name | public |
+--------+----------+--------+
| 1 | A | 1 |
| 2 | B | 2 |
| 3 | C | 3 |
+--------+----------+--------+
3 rows in set (0.00 sec)
mysql> select * from two;
+--------+----------+--------+
| two_id | two_name | public |
+--------+----------+--------+
| 1 | J | 1 |
| 2 | K | 2 |
| 3 | L | 3 |
+--------+----------+--------+
3 rows in set (0.00 sec)
自然查询natural join,自动将名称相同(数据类型可以不同)的列合并到一列,并且将具有相同公共值(1和’1’也认为相同)的行合并为一行
mysql> select * from one natural join two;
+--------+--------+----------+--------+----------+
| public | one_id | one_name | two_id | two_name |
+--------+--------+----------+--------+----------+
| 1 | 1 | A | 1 | J |
| 2 | 2 | B | 2 | K |
| 3 | 3 | C | 3 | L |
+--------+--------+----------+--------+----------+
3 rows in set (0.00 sec)
![SQL优化SQL语句优化的目的[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)