2023-11-10第06章_多表查询

-- 六.多表查询
-- DESC employees ; -- 查表
-- 1.等值连接 非自连 （使用= 2张表）
-- 2.非等值连接 非自连 （不使用= 2张表）
-- 3.自连 写错了4次 注意下 manager_id 表示直属上司id

-- 4.1.交集. 特别注意;符号 会导致语句出错
-- SELECT last_name,department_name,city
-- FROM employees
-- JOIN departments ON employees.employee_id = departments.department_id
-- JOIN locations ON departments.location_id = locations.location_id
-- 以上全内连接 下面是外连接 不支持（+）
-- 4.2.左并集 左外
-- DESC employees
-- SELECT last_name ,department_name,employees.department_id ,employee_id
-- FROM employees LEFT JOIN departments
-- ON employees.department_id = departments.department_id
-- 4.3.右并集 右外
-- SELECT last_name ,department_name,employees.department_id ,employee_id
-- FROM employees RIGHT JOIN departments
-- ON employees.department_id = departments.department_id
-- 满外连接。【UNION】会执行去重操作 [UNINSTALL]不会执行去重操作 基本用这个
-- --4. 4.左非交集
-- -- DESC employees
-- SELECT last_name ,department_name ,employee_id,departments.department_id
-- FROM employees LEFT JOIN departments
-- ON employees.department_id = departments.department_id
-- WHERE ISNULL(departments.department_id);
-- -- 4.5.右非交集
-- -- DESC employees
-- SELECT last_name ,department_name ,employee_id
-- FROM employees RIGHT JOIN departments
-- ON employees.department_id = departments.department_id
-- WHERE ISNULL(employees.department_id)
-- 4.6.左非交集 + 右非交集 = 非交集 UNION ALL. 上下SELECT 对象要一样否则出错
-- DESC employees
-- SELECT last_name ,department_name,employees.department_id ,employee_id
-- FROM employees LEFT JOIN departments
-- ON employees.department_id = departments.department_id
-- WHERE ISNULL(departments.department_id)
-- UNION ALL
-- SELECT last_name ,department_name,employees.department_id ,employee_id
-- FROM employees RIGHT JOIN departments
-- ON employees.department_id = departments.department_id
-- WHERE ISNULL(employees.department_id)
-- 4.7.全集 左非交集 + 右并集 / 右非交集 + 左并集
-- SELECT last_name ,department_name,employees.department_id ,employee_id
-- FROM employees LEFT JOIN departments
-- ON employees.department_id = departments.department_id
-- UNION ALL
-- SELECT last_name ,department_name,employees.department_id ,employee_id
-- FROM employees RIGHT JOIN departments
-- ON employees.department_id = departments.department_id
-- WHERE ISNULL(employees.department_id)
-- 或者
-- SELECT last_name ,department_name,employees.department_id ,employee_id
-- FROM employees LEFT JOIN departments
-- ON employees.department_id = departments.department_id
-- WHERE ISNULL(departments.department_id)
-- UNION ALL
-- SELECT last_name ,department_name,employees.department_id ,employee_id
-- FROM employees RIGHT JOIN departments
-- ON employees.department_id = departments.department_id

-- 5. NATURAL JOIN 自动查询两张连接表中 所有相同的字段
-- 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;
-- FROM employees e NATURAL JOIN departments d;

-- 6.USING JOIN departments d USING (department_id)
-- SELECT employee_id,last_name,department_name
-- FROM employees e JOIN departments d
-- -- ON employees.department_id = departments.department_id
-- USING (department_id);

-- . 7.UNION ALL / UNION 联合

-- 8.注意点
-- 概念笛卡尔积交叉连接(避免错误) 把任意表进行连接，即使这两张表不相关(在WHERE 加入有效的连接条件)
-- 如果我们使用了表的别名 在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。
-- 对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前表的别名(或表名)进行限定
-- 需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在， 只能用 (+) 表示。
-- 尽量使用UNION ALL语句，以提高数据查询的效率。
-- 超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致;多表关联查询时， 保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。