SQL连接查询JOIN原理与实战

一. JOIN原理

在连接查询中，查询结果集最大的情况是笛卡尔积，但是可以通过连接条件或者where 条件控制结果集。比如要查询学生表 student 以及学生的成绩表 score，这里要用到 left join。

首先在 student 表找第一条记录，然后从头开始扫描 score 表，比较 ON 后面的匹配条件，匹配成功后将 student 表的该条记录和 score 表该条记录拼接在一起，形成一条新的记录，然后扫描 score 的下一条记录进行同样的操作，直到 score 表的所有记录都扫描完。

继续查找 student 表第二条记录，从头开始扫描 score 表，比较 ON 后面的匹配条件，匹配成功后将 student 表的该条记录和 score 表的该条记录拼接在一起，形成一条新的记录，重复如上操作，直到 student 表所有记录处理完毕为止。

各种连接查询的基本原理是相似的，不同点在于关联条件不同，数据组合的方式不同，在此查询算法的基础上进行相关的优化。

二. inner join

• inner join 不区分左表和右表，只要关联条件匹配成功，则会形成一条新的联合记录。

SELECT t1.aid, t2.bid
FROM (
         SELECT 1 AS aid
         UNION ALL
         SELECT 1 AS aid
     ) t1
         JOIN
     (
         SELECT 1 AS bid
         UNION ALL
         SELECT 1 AS bid
         UNION ALL
         SELECT 2 AS bid
     ) t2 ON t1.aid = t2.bid

-- 结果
1,1
1,1
1,1
1,1

三. left outer join

场景1：关联条件中右表的字段是唯一的，得到的新的记录数和左表完全相同

• 在ON后面的连接条件中没有找到关联的记录时，左表的记录保留，此时右表的每个字段值都为NULL。

场景2：关联条件中右表的字段可能重复，得到的新的记录数大于左表的记录数

• 在扫描记录时，右表会出现重复的关联值，此时就会出现重复的新记录，这时左表的记录也会重复。

SELECT t1.aid, t2.bid
FROM (
         SELECT 1 AS aid
         UNION ALL
         SELECT 2 AS aid
     ) t1
         LEFT JOIN
     (
         SELECT 3 AS bid
     ) t2 ON t1.aid = t2.bid
-- 结果
1,NULL
2,NULL


SELECT t1.aid, t2.bid
FROM (
         SELECT 1 AS aid
         UNION ALL
         SELECT 2 AS aid
     ) t1
         LEFT JOIN
     (
         SELECT 2 AS bid
         UNION ALL
         SELECT 2 AS bid
     ) t2 ON t1.aid = t2.bid
-- 结果
1,NULL
2,2
2,2

四. right outer join

右连接。左连接相比，主表变成了右表，其他的逻辑一次推导成立

五. full join

功能：表A 和 表B full join，指定 ON 后面的关联条件为id，关联上的组成一条新记录，关联不上的同样保留这条记录，只是没关联上的一边用 NULL 代替。

• MySQL 没有 full join，它用left join 和 union 来代替。
• 保留左表的所有数据，右表如果不能关联上，用 NULL 代替
• 保留右表的所有数据，左表如果不能关联上，用 NULL 代替
• UNION 去重左右边能关联上的数据，得到最终结果

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;

• Hive 有 full join

SELECT t1.aid, t2.bid
FROM (
         SELECT 1 AS aid
         UNION ALL
         SELECT 2 AS aid
     ) t1
         FULL JOIN
     (
         SELECT 2 AS bid
         UNION ALL
         SELECT 3 AS bid
     ) t2 ON t1.aid = t2.bid
-- 结果
1,NULL
2,2
NULL,3

六. union

功能：将两批记录合并成一个新表，同时去重，注意必须保持合并的字段数量和类型相同。

SELECT 2 AS aid
UNION ALL
SELECT 2 AS bid
-- 结果
2

七. union all

功能：将两批记录合并成一个新表，不会去重，注意必须保持合并的字段数量和类型相同。

SELECT 2 AS aid
UNION ALL
SELECT 2 AS bid
-- 结果
2
2

八. 查询优化

• 在使用 JOIN 查询时，尽量把连接条件写在 ON 后面，而不是 JOIN 后的 WHERE 后面。
• 尽量避免子查询，改用 join