查询设计之查询条件对结果的影响

在sql使用中，最常用的就是查询。 在实践过程中，丰富的查询场景也使得查询sql有多种写法，不同的写法会带来不同的效果
以下，就查询条件对结果的影响。

---

一、数据

---

二、需求

把查询 type = 'A’的 user 以及 type = ‘A’ 的 department、person 关联的user 查出来

---

三、设计

1、随心所欲，互不影响

union查询，把每个表符合条件的数据单独查询，最后用union进行拼接
使用背景：一般是返回结果对应的表没有关联关系，不能组合或者关联查询
设计思路：每个表的条件单独写，相互不影响，逻辑清晰
注意问题：union all 性能高，但是会重复; 所有数据块的返回字段必须一致

SELECT
	u.id as user_id,u.name as user_name,u.type as user_type,
	p.id as person_id,p.name as person_name,p.type as person_type,
	d.id as department_id,d.name as department_name,d.type as department_type
from user u left join person p on u.personid = p.id 
left JOIN department d on u.departmentid = d.id 
WHERE u.type = 'A'
union 
SELECT
	u.id as user_id,u.name as user_name,u.type as user_type,
	p.id as person_id,p.name as person_name,p.type as person_type,
	d.id as department_id,d.name as department_name,d.type as department_type
from user u left join person p on u.personid = p.id 
left JOIN department d on u.departmentid = d.id 
WHERE p.type = 'A'
union 
SELECT
	u.id as user_id,u.name as user_name,u.type as user_type,
	p.id as person_id,p.name as person_name,p.type as person_type,
	d.id as department_id,d.name as department_name,d.type as department_type
from user u left join person p on u.personid = p.id 
left JOIN department d on u.departmentid = d.id 
WHERE d.type = 'A'
order by user_id

结果：

2、犬牙交错，只要结果

left join查询，最后用or条件挑选符合的数据
使用背景：left join之后包含所有结果，or里面的条件能涵盖所有结果
设计思路：left join只做外键约束，不写额外条件，所有条件写在where 里面
注意问题：不设置or条件必须包含所有结果，or限定被left join的表字段结果等同inner join

-- 对查询结果进行过滤(包含连接对象条件时，理论上和 inner jion 相同)
SELECT
	u.id as user_id,u.name as user_name,u.type as user_type,
	p.id as person_id,p.name as person_name,p.type as person_type,
	d.id as department_id,d.name as department_name,d.type as department_type
from user u 
left join person p on u.personid = p.id 
left JOIN department d on u.departmentid = d.id
WHERE u.type = 'A' or p.type = 'A' or d.type = 'A' 
order by user_id

结果：

3、条件复杂，计算结果

left join查询，case 挑选，最后用case 结果作为条件挑选符合的数据
使用背景：通过join on 和 where 的条件过滤都不如意，需要一些更复杂的判断
设计思路：select 嵌套查询，内层case 进行结果处理，外层对 case 的值做过滤
注意问题：注意别名对应，case每种情况需要考虑周全

-- 对结果集进行计算(判断)后输出
SELECT * FROM (
SELECT
	u.type,
	u.id as user_id,u.name as user_name,u.type as user_type,
	p.id as person_id,p.name as person_name,p.type as person_type,
	d.id as department_id,d.name as department_name,d.type as department_type,
	(case when (
		p.id is NULL and d.id is NULL and u.type = 'A' 
		or p.id is not NULL
		or d.id is not NULL
		) then 1 else 0 end
	) hhh
from user u 
left join person p on u.personid = p.id  and p.type = 'A'
left JOIN department d on u.departmentid = d.id and d.type = 'A'
) result where result.hhh = 1
order by user_id