SQL JOINS
一、7种SQL JOINS的实现
```sql
SELECT * from t_emp e JOIN t_dept d ON e.deptId= d.id
SELECT * from t_emp e LEFT JOIN t_dept d ON e.deptId= d.id
SELECT * from t_emp e RIGHT JOIN t_dept d ON e.deptId= d.id
SELECT * from t_emp e LEFT JOIN t_dept d ON e.deptId= d.id WHERE e.deptId IS NULL
SELECT * from t_emp e RIGHT JOIN t_dept d ON e.deptId= d.id WHERE e.deptId IS NULL
SELECT * from t_emp e LEFT JOIN t_dept d ON e.deptId= d.id
UNION
SELECT * from t_emp e RIGHT JOIN t_dept d ON e.deptId= d.id
SELECT * from t_emp e LEFT JOIN t_dept d ON e.deptId= d.id WHERE e.deptId IS NULL
UNION ALL
SELECT * from t_emp e RIGHT JOIN t_dept d ON e.deptId= d.id WHERE e.deptId IS NULL
```
二、SELECT执行顺序
```SQL
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
```
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
三、子查询
```sql
查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
#方式一：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(dept_avgsal)
FROM (
SELECT AVG(salary) dept_avgsal
FROM employees
GROUP BY department_id
) avg_sal
)
);
#方式二：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id )
);
#方式三：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
)
)
#方式四：
SELECT d.*,dept_avg_sal.avg_sal
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
) dept_avg_sal
WHERE d.department_id = dept_avg_sal.department_id
```
四、视图
```sql
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
```
总结：可以将视图理解为存储起来的SELECT语句