内连接/左外连接/右外连接

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

转载于:https://my.oschina.net/javaGeeker/blog/673642