mysql> desc student ;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| student_id | int(11) | NO | PRI | NULL | auto_increment |
| student_name | varchar(255) | YES | | NULL | |
| student_class | varchar(255) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
mysql> desc score;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| score | int(11) | YES | | NULL | |
| student_id | int(11) | YES | | NULL | |
| object | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
student表数据:
mysql> select * from student;
+------------+--------------+---------------+
| student_id | student_name | student_class |
+------------+--------------+---------------+
| 1 | zhangsan | 0801 |
| 2 | lisi | 0802 |
| 3 | wangwu | 0803 |
| 4 | tom | 0804 |
+------------+--------------+---------------+
score表数据:
mysql> select * from score;
+----+-------+------------+--------+
| id | score | student_id | object |
+----+-------+------------+--------+
| 1 | 80 | 1 | yuwen |
| 2 | 90 | 2 | shuxue |
| 3 | 88 | 3 | lishi |
+----+-------+------------+--------+
内连接(等值连接):只有两个表相匹配的行才能在结果集中出现——交集
mysql> select st.student_id, st.student_name, sc.score, sc.score, sc.object
-> from student st, score sc
-> where st.student_id = sc.student_id;
+------------+--------------+-------+-------+--------+
| student_id | student_name | score | score | object |
+------------+--------------+-------+-------+--------+
| 1 | zhangsan | 80 | 80 | yuwen |
| 2 | lisi | 90 | 90 | shuxue |
| 3 | wangwu | 88 | 88 | lishi |
+------------+--------------+-------+-------+--------+
左外连接(左边的表不加限制):以左边的表(student st left)student为主表,score为从表。在查询结果中全部展示主表的信息。
mysql> select st.student_id, st.student_name, sc.object, sc.score
-> from student st
-> left join score sc
-> on st.student_id = sc.student_id;
+------------+--------------+--------+-------+
| student_id | student_name | object | score |
+------------+--------------+--------+-------+
| 1 | zhangsan | yuwen | 80 |
| 2 | lisi | shuxue | 90 |
| 3 | wangwu | lishi | 88 |
| 4 | tom | NULL | NULL |
+------------+--------------+--------+-------+
右外连接(右边的表不加限制):以左边的表(student st right)student为从表,score为主表。在查询结果中全部展示主表的信息。
mysql> select st.student_id, st.student_name, sc.object, sc.score
-> from student st
-> right join score sc
-> on st.student_id = sc.student_id;
+------------+--------------+--------+-------+
| student_id | student_name | object | score |
+------------+--------------+--------+-------+
| 1 | zhangsan | yuwen | 80 |
| 2 | lisi | shuxue | 90 |
| 3 | wangwu | lishi | 88 |
+------------+--------------+--------+-------+
全外连接:
mysql> select st.student_id, st.student_name, sc.object, sc.score
-> from student st
-> left join score sc
-> on st.student_id = sc.student_id
-> union
-> select st.student_id, st.student_name, sc.object, sc.score
-> from student st
-> right join score sc
-> on st.student_id= sc.student_id;
+------------+--------------+--------+-------+
| student_id | student_name | object | score |
+------------+--------------+--------+-------+
| 1 | zhangsan | yuwen | 80 |
| 2 | lisi | shuxue | 90 |
| 3 | wangwu | lishi | 88 |
| 4 | tom | NULL | NULL |
+------------+--------------+--------+-------+
性能优化:
1.显式的内连接和隐式的内连接,性能相差不大。
2.尽量用内连接而不使用外连接
3.使用左外连接或右外连接时,需要清楚 on 与 where 的执行顺序。
ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。 |
所以在使用 Left (right) join 的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行
如:
select * from A inner join B on B.name = A.name left join C on C.name = B.name and C.status>1 left join D on D.id = C.id and D.status=1 |
使用 ON 条件决定了从 LEFT JOIN的 C表中检索符合的所有数据行
4.尽量避免使用子查询,而用join