数据库Join查询：内联、左外联、右外联、取交集

原文： RelaxHeart网 - Tec博客: 库Join查询：内联、左外联、右外联、取交集

示例：

在MySQL创建两张表：a_table、b_table

随便插入几条数据：

组合两个表中的记录，返回关联字段相符的记录，也就是两个表的交集

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左表全部记录都会显示出来，右表只显示出满足条件的记录，不足的列全部显示NULL

right join 是 right outer join的简写，它的全程是右外连接，是外链接的一种。

右表全部记录都会显示出来，左表只显示满足条件的记录，不足的列全部显示NULL

有了内联、外联查询，有没有取并集查询呢？当然也有了：

注意：查询报错了！！！

左外查询 union 右外查询

UNION定义：UNION *** 作符用于合并两个或多个 SELECT 语句的结果集。

注意：

UNION 和 UNION ALL 区别：UNION ALL会列出所有结果，有重复，UNION没有重复

inner join: 如果查询的表中都有至少一个匹配，则返回行

left join: 即使右表中没有匹配，也从左表返回所有的行

right join: 即使左表中没有匹配，也从右表返回所有的行

full join: 只要其中一个表中存在匹配，就返回行,但是在mysql中不支持

union:合并多个查询语句的结果集（去重）

union all:合并多个查询语句的结果集（不去重）

更多文章： RelaxHeart网更多博文

join查询通常是代替子查询，典型的join查询是自连接，即自己和自己join起来查询数据。

练习一：

从下表中找出英语成绩=100的该同学的其他科目的成绩

方法一：子查询

方法二：自连接查询

练习二：

从下表中筛选出每个类别的销量前三的商品

方法一：

子查询

方法二：

自连接

关于上面自连接的逻辑解释：

fruit表自身左连接，会从笛卡尔积表中取出满足on条件的记录，并将左表中剩余部分也补足进去，这样的话，F.salecount<L_F.salecount,就会形成：最大的salecount的那个类别出现一次（这是左表中剩余部分补足进去的），第二大的salecount的那个类别在自身左连接过程中，出现一次，因为它只比最大的那个小，其次，第三大的那个salecount会出现两次，因此在group by 后的having中，对ID进行分组聚合后是count(id)<3可以得出各个分类中salecount排名前三的那些。

上面的解法有一个bug，如果水果类目里面，还有一个‘火龙果’价格也是866，那么销量第三高的樱桃的677就会选不出来，就得不到想要的每个类目的前三名。

问题出现在 having count()那里，因为将左表中的677与右表进行比较时，遇到右表的两个866，会返回两次677，这样就增加了677的count次数，遇到897时一次，遇到866时两次，这样就有了三次677，在进行having count（l.id）<3时就不会得到677。可以用右表来限制取值，对左表的l.id进行group by以后，与左表进行比较的右表中的比相应左表大的值也会划分进左表的group by里面，这时取having(distinct L_F.salecount)<3,逻辑是右表中比左表相应数据大的数据的distinct值小于3个，那么左表的值就排前三，比如，最表是677，右表是相应的866，866，897，distinct后是两个数值比左表大，那么左表的这个值就排行老三，就被取出来了，左表的866和897是同样的道理。

练习三：

person表：

movie表：

方法二：连接查询

JOIN标准语句, 与ARRAY JOIN 关系不大.

在子查询中执行JOIN查询. 在查询处理开始时, 子查询在 JOIN 以后指定, 他的结果保存在内存中. 然后从指定在FROM语句中的"左关联" 表中读取, 在读取过程中, 对于从"左关联" 表中读取的每行记录, 和从子查询结果("右关联")表中查询的数据满足USING中的匹配条件.

表名能够被指定来替代子查询. 这等于SELECT * FROM table subquery, 在特定情况下，当表有Join 引擎时 – 一个数组准备 join.

在JOIN中所有的列如果不需要，则从子查询中删除.

有如下几种类型的JOIN:

INNER 或 LEFT 类型: 如果 INNER 被指定，结果仅包含这些行，在右表中匹配的行. 如果 LEFT 被指定，任何在左表中的行没有匹配右表中的行都将被分配默认值 - zeros or empty rows. LEFT OUTER 可以被写来替代LEFTOUTER 不影响任何事情。

ANY 或 ALL 字符串: 如果ANY 被指定和右表有一些匹配的行，只有第一行发现被Join，. 如果 ALL 被指定，右表有一些匹配的行，数据将按照行数相乘.

使用 ALL 对应的 JOIN 语义. 使用ANY 是最优的. 如果右表仅有一个匹配行, ANY 和 ALL 的结果是相同的. 你必须指定 ANY 或 ALL (默认情况下，2个都不选择).

GLOBAL 分布:

当使用 JOIN语句, 查询将被发送至远程服务器. 子查询将被运行在每个节点上，为了让右表和JOIN查询运行在此表上. 换句话说, 右表单独运行在每个服务器上.

当使用 GLOBAL ... JOIN, 首先 请求服务器运行子查询来计算右表. 此临时表被传递到每个远程服务器, 查询运行在临时表上.

当使用 GLOBAL JOINs时需要小心. 更多信息, 请查看章节 "分布式子查询".

任意的JOINs都是有可能的. 例如, GLOBAL ANY LEFT OUTER JOIN.

当运行一个 JOIN时, 执行顺序没有优化，和其他的查询阶段相比. Join在WHERE过滤之前和聚合之前被执行. 为了设置处理顺序, 我们推荐在一个子查询里运行一个JOIN子查询.

子查询不允许你设置名称或使用他们，对于从一个特定的查询中引用一个列. 指定在 USING 中的列必须在两个子查询中都有相同的名字, 其他的列必须单独命名. 在子查询中你可以使用别名来更改列的名称，和在子查询中的列 (例如别名使用 'hits' 和 'visits').

USING 语句指定一个或多个列进行Join, 建立这些列的等值列. 列的列表被设置不需要brackets. 因此不支持复杂JOIN条件.

右表 (子查询结果) 驻留在内存中. If 如果没有足够的内存，也不能运行 JOIN.

仅有一个JOIN 能够被指定在一个查询中. 为了运行多个 JOINs, 你能够放它们在子查询中.

每次一个查询运行相同的 JOIN, 子查询再次运行 – 结果不被缓存. 为了避免这个, 使用特定的'Join' 表引擎, 它是一个预处理数组，在内存中进行join *** 作. 对于更多信息, 请查看章节 "表引擎, Join".

如果你想要一个JOIN 来关联维度表 (这些是一些小表，包含维度属性, 如营销活动的名称), 一个 JOIN 可能并不是特别合适，由于 bulky 的语句，右表对于每个查询重新访问. 在这些场景下, 有一个 "外部字典" 特性，你应该使用它来替换 JOIN. 更多信息, 请查看章节 "外部字典".

---