数据库多表查询

数据库多表查询

#多表查询 需要有正确连接条件 -- SELECT employee_id,department_name -- FROM employees,departments #连接条件 -- WHERE employees.department_id = departments.department_id; #如果查询语句中才出现了多个表中都存在的字段，则必须指明此字段的所在表 -- SELECT employees.employee_id,departments.department_name -- FROM employees,departments -- WHERE employees.department_id = departments.department_id;

#建议：从SQL优化的角度，建议多表查询时，每个字段前都指明所在表。

#为了可读性更好可以给表起别名，需要注意的是起了别名后只能使用别名，不能再使用原名 -- SELECT emp.employee_id,dept.department_name -- FROM employees emp,departments dept -- WHERE emp.department_id = dept.department_id;

#如果有n个表实现多表查询，至少需要n-1个连接条件 #查询员工的employee_id,last_name,department_name,city -- SELECT e.employee_id,e.last_name,d.department_id,l.city -- FROM employees e,departments d,locations l -- WHERE e.department_id = d.department_id -- AND d.location_id = l.location_id;

#多表查询的分类 #角度1：等值连接 vs 非等值连接 #非等值连接

-- SELECT e.last_name,e.salary,j.grade_level -- FROM employees e,job_grades j -- WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

#角度2：自连接 vs 非自连接 #自连接

-- SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name -- FROM employees emp,employees mgr -- WHERE emp.employee_id = mgr.manager_id;

#角度3：内连接 vs 外连接

#内连接：合并具有同一列的两个以上的表的行，结果集中不包含几个表中不匹配的行 -- SELECT employee_id,department_name -- FROM employees e,departments d -- WHERE e.department_id = d.department_id;

#外连接：合并具有同一列的两个以上的表的行，结果集中包含几个表中不匹配的行 #外连接分类：左外连接，右外连接，满外连接

#左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足的条件的行 #右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足的条件的行

#SQL99语法使用 JOIN...ON实现多表联查 #内连接 -- SELECT employee_id,department_name -- FROM employees e JOIN departments d -- ON e.department_id = d.department_id;

-- SELECT employee_id,department_name,city -- FROM employees e JOIN departments d -- ON e.department_id = d.department_id -- JOIN locations l -- ON d.location_id = l.location_id;

#左外连接 -- SELECT last_name,department_name -- FROM employees e LEFT JOIN departments d -- ON e.department_id = d.department_id;

#右外连接 -- SELECT last_name,department_name -- FROM employees e RIGHT JOIN departments d -- ON e.department_id = d.department_id;

#满外连接：MySQL不支持full outer join -- SELECT last_name,department_name -- FROM employees e FULL OUTER JOIN departments d -- ON e.department_id = d.department_id;

#UNION 和 UNION ALL #UNION 会执行去重操作 #UNION ALL 不执行去重操作 #结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去重的数据 尽量使用UNION ALL语句来提高效率

#七种JOIN的实现 #内连接： -- SELECT employee_id,department_name -- FROM employees e 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;

#右外连接： -- SELECT employee_id,department_name -- FROM employees e RIGHT 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;

#只留右表独立部分 -- SELECT employee_id,department_name -- FROM employees e RIGHT JOIN departments d -- ON e.department_id = d.department_id -- WHERE e.department_id IS NULL;

#满外连接 -- SELECT employee_id,department_name -- FROM employees e LEFT 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.department_id IS NULL;

-- 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;

-- 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 -- WHERE e.department_id IS NULL;

#SQL99语法新特性：1.自然连接 2.USING #自然连接 -- SELECT employee_id,last_name,department_name -- FROM employees e NATURAL JOIN departments d;

#USING -- SELECT employee_id,last_name,department_name -- FROM employees e JOIN departments d -- USING(department_id);