sql必背问题 ：各种 join 的区别，执行顺序，on 和 where having 的区别，笛卡尔积与效率。附近期我和花花的两场面试真题。

sql

1.各种 join 的区别

• inner join：返回两个表中具有匹配值的记录

• left (outer) join：返回左表中的所有记录以及右表中的匹配记录

• right (outer) join：从右表返回所有记录，并从左表返回匹配的记录

• Full (outer) join：当左表或右表中存在匹配项时，返回所有记录

• cross join：结果是笛卡尔积

2.执行顺序

(5)SELECT DISTINCT TOP(<top_specification>) <select_list>                     
(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>
(2)WHERE <where_predicate>
(3)GROUP BY <group_by_specification>
(4)HAVING <having_predicate>
(6)ORDER BY <order_by_list>

• FROM ：不论是什么join，先求笛卡尔积，生成虚拟表VT1-J1
• ON：对VT1-J1根据ON子句进行筛选，插入到VT1-J2
• JOIN：如果是outer join，要将中没有找到匹配的行，作为外部行添加进VT1-J2生成VT1-J3。
• WHERE：对得到的VT1进行行筛选，插入到VT2
• GROUP BY：将VT2中的行进行分组，生成VT3
• AVG/SUM/MEAN
• HAVING：对VT3的分组进行筛选，并将符合条件的组插入到VT4中。
• SELECT
• DISTINCT
• ORDER BY
• LIMIT, OFFSET

这里引出2个细节，比如计算班里女生的平均成绩，

1. where 和 having 的区别：求女生平均成绩，用 where 过滤掉男生因为其执行在求mean之前。

2. **having 是在 select 之前执行的步骤，不可以引用别名。**比如select mean(gpa) as "平均成绩"from... + having mean(gpa)>3.5 。同理 order by 可以引用别名。

3.on 和 where 的区别

1. 执行上的区别，见上一题。

   首先两个表做一个笛卡尔积，on后面的条件对该笛卡尔积做过滤形成临时表，如果没有where就直接返回结果，如果有where就对上一步的临时表再进行过滤。

2. 执行顺序差异导致的结果就是，条件放在 on/where 返回结果集可能不一致，具体来说：

   • 在inner join 时，条件放在on中和where中，返回的结果集是相同的。

   • 但在left/right/full join时，返回的结果集可能不同。比如 left join 筛选条件为右表的某列取值，①使用on时无论左边表取值多少，左边表中的所有记录都会返回；②where是在求出笛卡尔积之后筛选，这时已经没有left join的含义，即使来自左边的记录在不满足条件时也会被筛掉，具体例子见这里。

4.笛卡尔积及其解决

笛卡尔积，即集合A中元素和集合B中元素相结合的所有可能性，两张表的数据量都比较大的话，笛卡尔积会占用很大的内存空间。

select ... from table1 cross join table2;
select ... from table1, table2; --当join不用on条件时，就会返回笛卡尔积。

延伸问题，如何避免出现庞大冗余的数据。当表与表之间的关联不是一对一的，由于笛卡尔积的乘法关系，会导致sql效率大大降低。比如使用包含大量重复值的 id 列作为on的条件——如何解决，先select distinct再join，比如有复合主键要用全。

（待补充）

附录-完整面试题目

【京东健康-数据科学】

1. sql 各种join, left join 和 left outer join
2. 执行顺序 from on where group by having
3. on和where的区别
4. 窗口函数应用题，简单
5. 是否用过spark / hive
6. 什么时候出现笛卡尔积，怎么避免
7. 机器学习原理：决策树的分割思想，常见损失函数，gbdt xgb区别 gbdt rf区别，梯度消失及其解决
8. 10个箱子放苹果，1~1000任给一个数都能找到k个箱子苹果数之和对应，怎么放？（二进制）

【罗森-数据分析】

1. 业务题：一个公司要发行一款低糖的运动饮料 ，问需要考虑哪些因素
2. 低糖的运动饮料在哪里更好卖
3. 你的优势是什么？
4. 数据分析最重要的是什么
5. 你理解的数据分析是干嘛的
6. 各种 join 的区别
7. 执行的一般逻辑