四、mysql99语法的七种join的操作
MySQL虽然不支持满外连接，但是我们可以用其他方式弥补
1.union操作符
开发中能用union all就不要用union
这里是因为union中间有重复的项目，但是union all没有
2.具体实现方法
根据上面的图片
2.1 内连接
SELECT employee_id , department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
2.2 左外连接
SELECT employee_id , department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
2.3 右外连接
SELECT employee_id , department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;
2.4 左中图
注意：这里执行顺序是先where语句里面执行，所以得出d.DEPARTMENT_ID 为 NULL ，这样e.DEPARTMENT_ID = NULL的字段其实就是我们要的A表部分数据。
SELECT employee_id , department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.DEPARTMENT_ID IS NULL;
2.5 右中图
和左中图同理
SELECT employee_id , department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id IS NULL;
2.6 满外连接
方式1：
左中图 union all 右外连接（自动去重）
SELECT employee_id , department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.DEPARTMENT_ID IS NULL UNION ALL SELECT employee_id , department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;
方法2：
右中图 union all 左外连接（自动去重）
SELECT employee_id , department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id IS NULL UNION ALL SELECT employee_id , department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
方法三：
SELECT employee_id , department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.DEPARTMENT_ID IS NULL UNION ALL SELECT employee_id , department_name FROM employees e JOIN departments d ON e.department_id = d.department_id UNION ALL SELECT employee_id , department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id IS NULL;
五、mysql99语法的新特性
1.自然连接
我们先看看普通连接
employees 和 departments 只有两个相同的字段department_id和manager_id
SELECT employee_id , last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id AND e.manager_id = d.manager_id;
自然连接就是相同字段相等的属性保留
SELECT employee_id , last_name,department_name FROM employees e NATURAL JOIN departments d;
2.using
普通语法
SELECT employee_id , last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
SELECT employee_id , last_name,department_name FROM employees e JOIN departments d USING (department_id);
总结
上面就是多表查询常用的方法和语句，虽然不是全部，但是也是大多数情况能碰到的了，希望大家好好学习，最后能给我点个👍吗
