14.mysql连接（join）查询 交叉连接 内连接 外连接 自连接

连接（join）查询是将两个查询的结果以“横向对接”的方式合并起来的结果。
对比：联合查询 是将两个查询的结果以“纵向堆叠”的方式合并起来的结果。

14.1.连接查询概述

连接查询，是将两个查询(或表)的每一行，以“两两横向对接”的方式，所得到的所有行的结果。
即一个表中的某行，跟另一个表中的某行，进行“横向对接”，而得到一个新行。
如下图所示：
则他们对接（连接）之后的结果类似这样：

可见，假设：
表1有n1行，m1列；
表2有n2行，m2列；
则表1和表2“连接”之后，就会有：
n1*n2行；
m1+m2列。

select  ...  from  表1  [连接方式] join  表2  [on连接条件]  where ... ；

可见，连接查询只是作为from子句的“数据源”。
或者说，连接查询是扩大了数据源，从原来的一个表作为数据源，扩大为多个表作为数据源。
连接查询包括以下这些不同形式：
交叉连接，内连接，外连接（分：左外连接，右外连接）

14.2.交叉连接（cross join）

辅助理解连接到概念，实际用处不大

from  表1  [cross]  join  表2

1，交叉连接其实可以认为是连接查询的“完全版本”，即所有行都无条件地都连接起来了。
2，关键字“cross”可以省略；
3，交叉连接又称为“笛卡尔积”，通常应用价值不大。
4，交叉连接还有一种写法： from 表1， 表2；

14.3.内连接（inner join）

from  表1  [inner]  join  表2  on  连接条件

1，内连接其实是交叉连接的基础上，再通过on条件而筛选出来的部分数据。
2，关键字“inner”可以省略，但建议写上。
3，内连接是应用最广泛的一种连接查询，其本质是根据条件筛选出“有意义的数据”。

找出出所有商品及其所属类别。

-- 内连接查询：
SELECT * FROM `product` inner join product_type on product.protype_id = product_type.protype_id;
-- 也可以改写为：
SELECT * FROM product as p inner join product_type as t on p.protype_id = t.protype_id;

因为连接之后的数据表中有两个protype_id，为了区分，需要说明是哪个表中的protype_id；product.protype_id product_type.protype_id

找出所有价格大于5000的家用电器的商品的完整信息（含所属类别）;

SELECT * FROM product as p inner join product_type as t on p.protype_id = t.protype_id
where price > 5000 and protype_name = '家用电器';

找出所有价格大于5000的家用电器的商品的名称，价格和品牌

SELECT pro_name, price, pinpai 
    FROM product as p inner join product_type as t on p.protype _id = t.protype_id
    where price > 5000 and protype_name = '家用电器';

14.4.外连接

外连接分为左外连接和右外连接。

14.4.1.左外连接（left join）：

from  表1  left [outer] join  表2  on  连接条件

1，左外连接其实是保证左边表的数据都能够取出的一种连接。
2，左外连接其实是在内连接的基础上，再加上左边表中所有不能满足条件的数据
3，关键字“outer”可以省略。

找出所有类别及各类别中的商品（需列出类别名称，商品名称，价格，品牌和产地）

-- 演示左连接：
--找出所有类别及各类别中的商品（需列出类别名称，商品名称，价格，品牌和产地）
select protype_name, pro_name, price, pinpai, chandi 
    from product_type as t left join product as p on p.protype_id = t.protype_id

14.4.2.右外连接（right join）：

from  表1  right [outer] join  表2  on  连接条件

1，右外连接其实是保证右边表的数据都能够取出的一种连接。
2，右外连接其实是在内连接的基础上，再加上右边表中所有不能满足条件的数据。
3，关键字“outer”可以省略。

select * from join1 left join join2 on join1.f1 = join2.c1;

找出所有用户及其订单信息（需列出用户id，用户名，订单号，订单总价，订单地址）

找出所有用户及其订单信息

-- 演示右连接：
--找出所有用户及其订单信息（需列出用户id，用户名，订单号，订单总价，订单地址） 
select user_info.user_id, user_name, order_id, order_total, order_addr
    from order_info  right join user_info on user_info.user_id = order_info.user_id;

因为是显示用户信息，有的用户没有订单，也要显示，
所以 右连接right user_info
左连接 user_info left

-- 演示左连接：
--上述语句，也可以写为如下形式，结果一样：
select user_info.user_id, user_name, order_id, order_total, order_addr
    from user_info  left join order_info on user_info.user_id = order_info.user_id;

可见：
左连接，右连接，其实是可以互换的——无非是把两个表的顺序调换一下。

14.5.自连接

自连接不是一种新的连接形式，而只是一个表“自己跟自己连接”，这怎么做到呢？

from  表1  as  a  [连接形式]  join  表1  as  b  on  a.xx字段1=b.xx字段名

1，自连接其实还是两个表连接，只是将一个表用不同的别名，当做两个表。
2，自连接适用于一个表中的某个字段的值“来源于”当前表的另一个字段的情况。

地区表如下所示：
id area_name parent_id
1北京市 0
2河北省 0
3山东省 0
4石家庄 2
5保定 2
6衡水 2
7济南 3
8青岛 3
9烟台 3
… …
要求查询每个城市及其所在省份，结果类似如下所示：
城市 省份
石家庄 河北省
保定 河北省

思路：

select  a.area_name, b.area_name  from area as a  join  area as b  on a.parent_id = b.id;

需求稍作调整：

---找出所有省份及其下属城市。
select province.area_name, city.area_name  
    from area as province join area as city on province.id = city.parent_id;

-- （如果还要包括不含下属城市的省级）；
select province.area_name, city.area_name  
    from area as province left join area as city on province.id = city.parent_id
    where province.parent_id = 0;